next without for

Hi,

I'm trying to loop through the sheets of the workbook. So the logic is if the sheets name matches Dino List's range("E" & i). value then it copies whole range and pastes it onto the matching named sheet. But I'm stuck with the for loop on line 'Next i'. It says 'next without for'. Don't know where I'm wrong?

Sub dinosaur()

Dim i, j As Integer
Dim diet As String
Dim ws As Worksheet
Dim lastrow As Long

 lastrow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
 

Sheets("Dino List").Activate

For Each ws In ActiveWorkbook.Worksheets
   For i = 2 To lastrow
        diet = Sheets(1).Range("E" & i).Value
        If ws.Name = diet Then
        Sheets(1).Range("A:K").Copy
        Worksheets(diet).Activate
        ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
        ActiveSheet.Paste
   Next i
Else: Next ws
End If
End Sub

Open in new window

LVL 1
sumit yadavAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul JacksonSoftware EngineerCommented:
try this:
Sub dinosaur()

Dim i, j As Integer
Dim diet As String
Dim ws As Worksheet
Dim lastrow As Long

 lastrow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
 

Sheets("Dino List").Activate

For Each ws In ActiveWorkbook.Worksheets
   For i = 2 To lastrow
        diet = Sheets(1).Range("E" & i).Value
        If ws.Name = diet Then
           Sheets(1).Range("A:K").Copy
           Worksheets(diet).Activate
           ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
           ActiveSheet.Paste
        End If
   Next i
Next ws
End Sub

Open in new window

0
sumit yadavAuthor Commented:
Hi paul thanks but I'm not able to satisfy the condition to loop through the sheets
if If ws.Name <> diet then loop to other sheet
0
Paul JacksonSoftware EngineerCommented:
Ok if I'm understanding you correctly shouldn't it be more like this :

Sub dinosaur()

Dim i, j As Integer
Dim diet As String
Dim ws As Worksheet
Dim lastrow As Long

 lastrow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
 

Sheets("Dino List").Activate

For i = 2 To lastrow
    diet = Sheets(1).Range("E" & i).Value
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = diet Then
           Sheets(1).Range("A:K").Copy
           Worksheets(diet).Activate
           ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
           ActiveSheet.Paste
        End If
    Next ws
Next i
End Sub

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Maybe you need this...

Sub dinosaur()

Dim i, j As Integer
Dim diet As String
Dim ws As Worksheet
Dim lastrow As Long

lastrow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
 
Sheets("Dino List").Activate

For i = 2 To lastrow
     diet = Sheets(1).Range("E" & i).Value
     On Error Resume Next
     Set ws = Sheets(diet)
     On Error GoTo 0
     If Not ws Is Nothing Then
        Sheets(1).Range("A:K").Copy
        ws.Activate
        ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
        ActiveSheet.Paste
        Application.CutCopyMode = 0
     End If
     Set ws = Nothing
Next i

End Sub

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sumit yadavAuthor Commented:
Hi Paul and Neeraj for your solution.
thanks
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Sumit! Glad we could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.