Looping through worksheets

**terkel** · 10-14-2008, 03:30 AM

Hi there
I’m trying to copy some data from each sheet in WorkbookA, except for the first sheet which is called “Menu”, into a single sheet in WorkbookB. I’m trying to loop through the worksheets in WorkbookA but don’t know how to exclude the first sheet. The code for copying and pasting works fine. It’s just the looping (as always) I’m having trouble with.

The code I’m trying is:

Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = Workbooks("Per02.xls")


    For Each ws In wb.Worksheets
    If Not Workbooks("Per02.xls").Worksheets("Menu") Then

	Code to copy and paste

        End If
    Next ws

End Sub

This produces an error message ‘Object doesn’t support this property or method’ and debugging highlights the line:

If Not Workbooks("Per02.xls").Worksheets("Menu") Then

I know this code is incorrect but don’t know what to use. Assistance from this forum would be greatly appreciated.

Many thanks

Terkel

**xld** · 10-14-2008, 03:50 AM

Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = Workbooks("Per02.xls")


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

	    Code to copy and paste
        End If
    Next ws
End Sub

**terkel** · 10-17-2008, 02:53 AM

Hi XLD
Thanks very much for your reply. Just to let you know that your code works fine but my code to loop through the worksheets is up the mud!. I'll post again but have run out of time for now and am away for 2 weeks.

Terkel