Hello,
I was hoping someone could help me out? I put together the attached file and code to consolidate information. The macro is running through the tabs and pasting them to the tab I specify but it is not stacking the data to consolidate all of the information to one tab. Instead it copies each tab and pastes completely over the data each time. I want it to copy sheet1 and paste it to sheet 30. Then copy sheet2 and paste it to the bottom of sheet 30 below the area it just pasted for sheet1. Any help is much appreciated.
Sub loopTotal()
Dim wk As Worksheet
Dim wks As Worksheet
Dim nDatarows As Long
Dim nLastRow As Long
For Each wk In ThisWorkbook.Worksheets
wk.Activate
Set wks = ThisWorkbook.Sheets("Sheet30")
wks.Range("A2", "BC65536").Clear
Range("A1", "Y65000").Copy
nDatarows = wks.Range("A65536").End(xlUp).Row + 1
wks.Range("A" & nDatarows).PasteSpecial xlPasteAll
nLastRow = wks.Range("A65536").End(xlUp).Row
For J = nDatarows To nLastRow
wks.Range("AB" & J) = strFileName
Next J
Application.CutCopyMode = False
Next wk
End Sub
Bookmarks