Hi All,

I am new to vba coding.
I have a workbook with many worksheets (tabs) and would like to create a macropage to copy & paste everything into one giant piece of worksheet.
The following code works fine for one of my template, but failed in the others (showed bug in lastcol=... for sub copypaste)
Since the worksheets are really complicated and had many merged cells, Here is what I have come up so far.
Could you please have a look and help
I also wish to copy and paste with exact functions (since many of the data contains referencing codes)
Thanks!!!

Brian

Sub macropage()
Dim ws As Worksheet
Sheets.Add.Name = "Macro Page"
Sheets("Macro Page").Select
For Each ws In Worksheets
If ws.Name <> "Macro Page" Then
ws.Select
ws.Application.Run "copypaste"
End If
Next
For Each ws In Worksheets
If ws.Name = "Macro Page" Then
ws.Select
ws.UsedRange.unmerge
End If
Next
End Sub
Sub copypaste()
Range("A1").Select
lastCol = ActiveSheet.Range("XFD1").End(xlToLeft).Column
lastrow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveWorkbook.Names.Add Name:="myRange", RefersTo:=ActiveSheet.Range("a1", ActiveSheet.Cells(lastrow, lastCol)).Select
Selection.Copy
Sheets("Macro Page").Select
ActiveCell.End(xlToLeft).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
End Sub