Copy Data to new workbook VBA - am I even close?

[andshep]

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

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

 

[MikeNewton]

Why don't you create a new sheet in your workbook, copy the required data then delete all but your new sheet and resave under a new name?

 

[GlennUK]

How about changin:

    Range("A6:O36").Select 
    Selection.Copy

to

   ws.Range("A6:O36").Copy