Hi there
This looping is driving me loopy. I hope some one can tell me what I’m doing wrong. I’ve searched the topic but can’t find anything that helps me sufficiently.
I have a workbook (“Per01.xls”) with 21 sheets in it, each one with a name tab with a different date plus one called ‘Menu’. These sheets are locked and password protected and I don’t know the password. I’m copying parts of each sheet into a new workbook(“Per01Combined.xls”). This workbook has 4 sheets in it called Wk 1, Wk2 etc. I copy the data I need from the first 5 sheets from “Per01.xls” into sheet Wk1 in “Per01Combined.xls”, then the next 5 sheets from “Per01.xls” into sheet Wk2 in “Per01Combined.xls etc. I have set up and modified a macro to do this and it works perfectly but need to run it 20 times with a lot of clicking backwards and forwards between workbooks so I want to be able to loop through the worksheets to do it. With the code I have it copies the page active sheet in “Per01.xls” 20 times into the active sheet in “Per01.xks combined”i nstead of copying each of the 20 pages just once.

Can anyone tell me the code I need to use to
a) have it copy each sheet just once; and
b) have it copy the first 5 sheets into Wk1, the next 5 into Wk2 etc.

Below is the code I am using:

Sub CombineTSO1Data()
    Dim IntBlankRow As Integer
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = Workbooks("Per01.xls")

    If ws.Name <> "Menu" Then
    For Each ws In wb.Worksheets

    Workbooks("Per01.xls").Activate
    Range("B1:AV1").Copy
    Workbooks("Per01Combined.xls").Activate
    IntBlankRow = WorksheetFunction.CountA(Range("A:A")) + 1
    Cells(IntBlankRow, 1).Select
    ActiveSheet.Paste
    Workbooks("Per01.xls").Activate
    Range("B2:AX2").Copy
   'Rest of code that does copying.  This all works ok
    Workbooks("Per01.xls").Activate

    Next ws
       End If


End Sub

As always, thanks for the great assistance received from this forum.