Copying multiple sheets to single destination sheet

[Krispy]

I would like to combine the data in my source workbook (4 sheets) to a single sheet in my destination workbook.

The macro will be in the destination workbook.

I would like to select the source workbook from all open workbooks and copy sheets "A", "B", "C", "D" to "Combined" sheet in destination workbook.

There are no blank rows in the data on Sheets A-D

The first row on sheets A-D contains Titles / Headers. Only the data needs to be copied and pasted (as values).

Sheet "A" should be pasted in row "2" of Destination Sheet ... row 1 will already contain Headers. Sheet "B" will then be pasted in the next row down etc.

Sheets A-D contain 25 columns of data (A-Y)

Thank you for your assistance with this

K

 

[Krispy]

I have made some progress combining the 4 sheets into 1

Sub A()
    Windows("Book1").Activate
    Sheets("A").Select
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Book2").Activate
    Sheets("Combined").Select
    Range("A2").Select
    ActiveSheet.Paste
    ActiveCell.SpecialCells(xlLastCell).Select
    Cells(ActiveCell.Row + 1, 1).Select
 
    Windows("Book1").Activate
    Sheets("B").Select
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Book2").Activate
    ActiveSheet.Paste
    ActiveCell.SpecialCells(xlLastCell).Select
    Cells(ActiveCell.Row + 1, 1).Select
 
    Windows("Book1").Activate
    Sheets("C").Select
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Book2").Activate
    ActiveSheet.Paste
    ActiveCell.SpecialCells(xlLastCell).Select
    Cells(ActiveCell.Row + 1, 1).Select
 
    Windows("Book1").Activate
    Sheets("D").Select
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Book2").Activate
    ActiveSheet.Paste
End Sub

I hoping to take the general premice from one of my previous posts to select the desired workbook from all open workbooks?

http://www.mrexcel.com/forum/showthread.php?t=370688

 

[jbeaucaire]

Perhaps this is simple enough, and will work on more than 4 sheets if needed:

Sub A()
Dim lastrow As Long, ws As Worksheet
Workbooks("Book1.xls").Activate

For Each ws In Worksheets
ws.Activate
lastrow = ws.UsedRange.Rows.Count
    Range("A2:A" & lastrow).Copy
    Workbooks("Book2.xls").Sheets("Combined").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
    Application.CutCopyMode = False
Next ws
End Sub