For each loop through multiple worksheets with specific exclusions

[Robert E Lee]

I have the following code which works perfectly. What I now wish to do is loop through all worksheet sin Wb except sheet1. I have tried to use a For Each Loop and an If statement trying to exclude Sheet1 but it wil not compile with the message that I have a next without a for. I have tried many permutations without success.Aany help appreciated

Wb.Sheets("Sheet2").Select
                          
R = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
BudgetCode = Wb.Sheets("Sheet2").Range("A2")

Wb2.Activate
Range("D2").Select

R1 = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
Set TargetRange = Range(ActiveCell, ActiveCell.Offset(R, 0))
For Each c In TargetRange
                            If c = BudgetCode Then
                            
                            Set SelectRange = Range(c.Offset(0, -2), (c.Offset(0, 3)))
                            
                            SelectRange.Copy Destination:=Wb.Sheets("Sheet2").Range("A" & R)
                            R = R + 1
                            End If
                
Next c

End Sub

Many thanks with apologies for forgetting the code tags

Rob

 

[Fluff]

Can you show us what you tried when you got the error?

 

[Robert E Lee]

Hi Fluff

Many thanks for your response.

I think I have solved the problem. My mistake was in putting too many Nexts and Ifs making the whole thing more complex than it needed to be

This was my solution

For Each rs In Wb.Sheets

              If rs.Name <> "Sheet1" Then
              
    R = rs.Range("A1").SpecialCells(xlCellTypeLastCell).Row
 BudgetCode = rs.Range("A2")

Wb2.Activate
Range("D2").Select

R1 = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
Set TargetRange = Range(ActiveCell, ActiveCell.Offset(R, 0))
For Each c In TargetRange
                            If c = BudgetCode Then
                            
                            Set SelectRange = Range(c.Offset(0, -2), (c.Offset(0, 3)))
                            
                            SelectRange.Copy Destination:=rs.Range("A" & R)
                            R = R + 1
                            End If
 
Next c
End If
Next rs

Thanks again

Rob

 

[Fluff]

Glad you figured it out & thanks for the feedback.

PS
with code tags the 2nd tag should be [/code] rather than