VBA HELP - LOOP THROUGH FILES

[cooolpramod]

Dear All,

I have following problem, please find the details below:

This is the piece of code that I am using.
The lines I have marked in red are basically the sheets where i want to paste my data.
The way I have done it now works fine but in case anyone adds or removes the sheet this code will not work.
I wanted to enter the name of the name of sheets in another excel and wanted to code to loop through those sheets and pass that range name as worksheet name but i wasn’t able to do it.
Can you explain how I can pass range name as a worksheet name?

Sub LoopThroughFiles()
    Dim MyObj As Object, MySource As Object, file As Variant, Dest As Range
    Set rDest = Sheets("lookup").Range("A1").Offset(1, 0)
    'Set sheetname = Sheets("lookup").Range("B1").Offset(1, 0)
    file = Dir("c:\Reports\")
    'Do While sheetname <> ""
    While (file <> "")
    For i = 28 To 32
         If InStr(file, "test") > 0 Then
         MsgBox "found " & file
         Exit Sub
      End If
         If file <> rDest Then
         
     MsgBox file & " " & "does not exists"
     Else
     Workbooks.Open "c:\Reports" & "\" & file
     Workbooks(file).Activate
     Sheets("Sheet1").Range("A1:IV65536").Select
     Selection.Copy
     'Range(Selection, Selection.End(xlDown)).Select
     'Range(Selection, Selection.End(xlToRight)).Copy
     'Selection.Copy
     'ActiveWorkbook.Close
     ThisWorkbook.Activate
     Sheets(i).Activate
     Sheets(i).Range("A1:IV65536").Select
     Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
     ‘Sheets (i).Range("A1").PasteSpecial Paste:=xlPasteValues
      End If
      Workbooks(file).Close
   file = Dir
    Set rDest = rDest.Offset(1, 0)
    'Set sheetname = sheetname.Offset(1, 0)
    Next
    Wend
End Sub

Please help.

[patel]

can you attach a sample file and explain better your goal ?