Copy and paste content of selected worksheets into a new worksheet

Question

I have an excel workbook with 10 worksheets. I would like to have a macro able to copy all the content of "Sheet3", "Sheet4", Sheet5", "Sheet6", and "Sheet7" into "Sheet10" that is empty at the moment. One after the other.

The content of "Sheet3", "Sheet4", Sheet5", "Sheet6", and "Sheet7" is not always the same, so I would like this macro to copy all the content not just a fixed range.

Use the excel built-in recording feature to record your action and modify the code based on your requirement and post the code with the exact problem that is stopping you to proceed further. — Barney
I cannot record it because the Consolidate function doesn't not allow me to simply copy and paste sheets into another one empty. — Anne

Kev Kev · Accepted Answer · 2017-03-22T04:05:43

Excel has a number of SpecialCells methods, one of which find the last used cell in the worksheet. For some reason, a macro will record this as .SpecialCells(xlLastCell) while the Excel help/reference states that it is .SpecialCells(xlCellTypeLastCell) and you can also use the value 11. All three of the macros below will select from the current cell to the last cell in the sheet.

Sub Macro1()
' Macro1 Macro
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
End Sub

Sub Macro2()
' Macro1 Macro
    Range(Selection, ActiveCell.SpecialCells(xlCellTypeLastCell)).Select
End Sub

Sub Macro3()
' Macro1 Macro
   Range(Selection, ActiveCell.SpecialCells(11)).Select
End Sub

Here is a very basic example that will copy the data from sheet1 and sheet2 and append it to sheet3.

Sub Macro4()
Sheets("Sheet1").Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Application.CutCopyMode = False
Selection.Copy

Sheets("Sheet3").Select
ActiveCell.SpecialCells(xlLastCell).Select
Selection.Offset(1, 0).Select
Selection.End(xlToLeft).Select
ActiveSheet.Paste

Sheets("Sheet2").Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Application.CutCopyMode = False
Selection.Copy

Sheets("Sheet3").Select
ActiveCell.SpecialCells(xlLastCell).Select
Selection.Offset(1, 0).Select
Selection.End(xlToLeft).Select
ActiveSheet.Paste
End Sub

Copy and paste content of selected worksheets into a new worksheet

1 Answers