VBA HELP - LOOP THROUGH FILES
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?
Code:
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.
