I have a workbook with a variable number of sheets, with variable names, (both variable from week to week), the sheets are all identical in format.
What I am trying to do is copy the same range from each sheet in the workbook to a new workbook. The following code is what I have tried with my limited knowledge. It is supposed to create a new workbook, and then copy the data from each sheet in the original workbook to the same sheet in the new workbook, adding the next lot of data to the bottom of the previous lot. Am I even close or is there a much better way to do this? Thanks for any/all help
EXcel 2003/Win XP Pro
What I am trying to do is copy the same range from each sheet in the workbook to a new workbook. The following code is what I have tried with my limited knowledge. It is supposed to create a new workbook, and then copy the data from each sheet in the original workbook to the same sheet in the new workbook, adding the next lot of data to the bottom of the previous lot. Am I even close or is there a much better way to do this? Thanks for any/all help
Code:
Sub CopyData()
Dim LastPasteRow As Range
Dim wb As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet
Set wb = ActiveWorkbook
Workbooks.Add
Set wb2 = ActiveWorkbook
wb.Activate
For Each ws In wb.Worksheets
Range("A6:O36").Select
Selection.Copy
wb2.Activate
Set LastPasteRow = Range("A65536").End(xlUp)
Range("A" & "LastPasteRow").Select
ActiveCell.Offset(1, 0).Select
Selection.Paste
Application.CutCopyMode = False
wb.Activate
Next ws
End Sub
EXcel 2003/Win XP Pro