How to combine data from different Excel tabs into master sheet using VBA

Question

I am using the below script to copy data from "Sheet1" of a multi-sheet Excel file into a master sheet of another Excel file. It's working perfectly for one sheet. Now I need to get it to run through all the sheets pasting the data into the next available row in the Master file.

Please note: all the sheets use the same password.

Please help!

Thanks,

Yohanan

Sub CopyRanges()
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim LastRow As Long

Set WB1 = ActiveWorkbook
Set WB2 = Workbooks.Open(WB1.Path & "\Datafile.xls")

Sheets("Sheet1").Unprotect ("Password1")

With WB2.Sheets("Sheet1")
  LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
End With
WB2.Sheets("71235").Range("B6:M" & LastRow).Copy
WB1.Sheets("Output").Range("A2").PasteSpecial xlPasteValues

Sheets("Sheet1").Protect ("FTCCTOR")
WB2.Close
End Sub

score 2 · Answer 1 · answered Mar 08 '16 at 22:32

Try this:

Sub CopyRanges()
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim LastRow As Long
Dim sht As Worksheet

Set WB1 = ActiveWorkbook
Set WB2 = Workbooks.Open(WB1.Path & "\Datafile.xls")

For Each sht In WB2.Sheets
    With sht
        .Unprotect ("Password1")
        LastRow = .Range("B" & .Rows.count).End(xlUp).Row
        WB1.Sheets("Output").Range("A" & WB1.Sheets("Output").Rows.count).End(xlUp).Resize(LastRow - 5, 12).value = .Range("B6:M" & LastRow).value
        .Protect ("Password1")
    End With
Next sht
WB2.Close
End Sub

When only wanting the values it is faster to assign the values than to copy them.

How to combine data from different Excel tabs into master sheet using VBA

1 Answers1