Hi!
I am trying to consolidate data from multiple workbooks into one sheet in workbook. For some reason, I cannot get excel to loop through those every sheet the workbook and copy the data. I am including my code below. Any suggestions would be appreciated!
I am trying to consolidate data from multiple workbooks into one sheet in workbook. For some reason, I cannot get excel to loop through those every sheet the workbook and copy the data. I am including my code below. Any suggestions would be appreciated!
Code:
Sub BOCIndex()
Workbooks.Open Filename:=ThisWorkbook.Path & "\BOC Cust.xlsx"
Dim LastRowRange As Long
Dim LastRowIndex As Long
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
LastRowRange = Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
LastRowIndex = Workbooks("Deposits Consolidation.xlsm").Worksheets("Index").Range("B1").End(xlDown).Row + 1
Range("A4", "D" & LastRowRange).Copy
Workbooks("Deposits Consolidation.xlsm").Worksheets("Index").Range("B" & LastRowIndex).PasteSpecial xlPasteValues
Next ws
'Workbooks("BOC Cust.xlsx").Close savechanges:=False
End Sub
Last edited by a moderator: