Start loop from defined sheet 1

[Romans58]

HI,Hoping someone can help.

I have the following code to loop through a workbook and copy cells from each worksheet to a pre defined sheet.

The problem I have is I would like to start the loop from the fourth worksheet in the work book not the first.

Any help would be great

Thanks

Private Sub Pages1()
Dim sht As Worksheet
Set myCell = Range("h6")
For Each sht In ActiveWorkbook.Worksheets
If sht.Name <> "test1" Then
Sheets("test1").Range(FindNewSpotInColumn(Range("B5")).Address) = sht.Range(myCell.Address)
End If
Next

 

[PHV]

Have you tried to replace this:
For Each sht In ActiveWorkbook.Worksheets
By this ?
For i = 4 To ActiveWorkbook.Worksheets.Count
Set sh ActiveWorkbook.Worksheets(i)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Romans58]

Thanks a heap. Did the trick beautifully.
Just needed to add the = in the set line
For i = 4 To ActiveWorkbook.Worksheets.Count
Set sh = ActiveWorkbook.Worksheets(i)

Invaluable
Thanks again

 

[SkipVought]

or this...

Private Sub PrintDispatchForms_Click()
    Dim sht As Worksheet, lNextRow As Long
    myCell = "h6"
    With Sheets("test1")
        lNextRow = .Range("B5").End(xlDown).Row + 1
        For i = 4 To ActiveWorkbook.Worksheets.Count
            If ActiveWorkbook.Worksheets(i).Name <> "test1" Then
                 .Cells(lNextRow, "B").Value = sht.Range(myCell).Value
                 lNextRow = lNextRow + 1
            End If
        Next
    End With
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244