Copying multiple worksheets into one onto a separate workbook

[tgraham]

I have a workbook (WB1) that contains multiple worksheets. I need my current workbook (WB2) to go into workbook (WB1) and copy the multiple worksheets and combine them into one worksheet in WB2.

I was able to work out... within the same workbook to combine all the worksheets into one but have had trouble copying and combining the data into an separate workbook.

Sub CombineData()


Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Master" And Sht.Range("A1").Value <> "" Then
Sht.Select
LastRow = Range("A65536").End(xlUp).Row
Range("A1", Cells(LastRow, "T")).Copy
Sheets("Master").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Else
End If
Next Sht




End Sub

Please help.

 

[Norie]

Perhaps.

Sub CombineData()
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim Sht As Worksheet
Dim LastRow As Long

    Set WB1 = ActiveWorkbook
    Set WB2 = Workbooks("NameOfTheWorkbookToCopyTo.xlsm")

    For Each Sht In WB1.Worksheets
        If Sht.Name <> "Master" And Sht.Range("A1").Value <> "" Then
            With Sht
                LastRow = .Range("A65536").End(xlUp).Row
                .Range("A1", .Cells(LastRow, "T")).Copy WB2.Sheets("Master").Range("A65536").End(xlUp).Offset(1, 0)
            End With
        End If
    Next Sht

End Sub

 

[Mel2016]

I have a similar question to this thread. I need to copy multiple columns (E:O). From all worksheets in the master but my code is only copying column E? I am really new to Vba and not sure of the syntax to copy mutiple columns. Thanks for the help.

Sub Master()

Dim ws As Worksheet
Application.ScreenUpdating = False
Sheets("Master").Activate

For Each ws In Worksheets
If ws.Name <> "Master" Then
ws.Range("E:O").Copy
ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0)

End If

Next ws

End Sub

 

[JLGWhiz]

@Mel2016,

Dim lastRow As Long
lastRow = ws.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
ws.Range("E1:O" & lr).Copy ActiveSheet.Cells(Rows.Count, 1).End(xlUp)(2)

Put the Dim statement at the top with your other declaration and insert the lastRow line after the For Each line. Substitute the copy line for the one you now have and the paste line.

We could both get in trouble for jumping in on this thread.