Next without For, Loop through -> copy and paste data into new sheet.

J

joey01_

New Member
Joined
May 10, 2022
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
Hi,

I'm trying to write some code that will loop through a column, and copy and paste specific data into a new sheet. So in the example below I want all rows the read "Navy" in the F column to be copy and pasted into a new sheet called Navy. The main issue I'm facing at the moment is the code skips the line ' navyexists = False'. If i put this part one line below instead I get the error: Next without For. Could anyone kindly help me work out the logic here? Thanks


VBA Code: 
Sub nikedata()
Dim c As Variant
Dim i As Integer
Dim Lastrow As Integer
Dim ws As Worksheet


Lastrow = Range("B" & Rows.Count).End(xlUp).Row


c = 0

navyexists = True
For i = 2 To Lastrow


If Range("F" & i) = "Navy" Then
c = c + 1
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "Navy" Then [B][I][COLOR=rgb(26, 188, 156)]navyexists = False[/COLOR][/I][/B]
End If
Next ws

If navyexists = False Then
 Sheets.Add.Name = "Navy"
 Sheets("nike_data_2022_09").Range("F" & i).EntireRow.Copy
 Sheets("Navy").Rows.Range("A" & c).PasteSpecial Paste:=xlValues
 End If
 
If navyexists = True Then
Sheets("nike_data_2022_09").Range("F" & i).EntireRow.Copy
Sheets("Navy").Rows.Range("A" & c).PasteSpecial Paste:=xlValues
End If



Next i




End Sub
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Hi joey01,

maybe

VBA Code: 
Sub nikedata()
' https://www.mrexcel.com/board/threads/next-without-for-loop-through-copy-and-paste-data-into-new-sheet.1223621/

Dim c As Long
Dim i As Long
Dim ws As Worksheet
Dim blnNavy As Boolean

Const cstrNavy As String = "Navy"

c = 0

blnNavy = False
For Each ws In ThisWorkbook.Worksheets
  If ws.Name = cstrNavy Then
    blnNavy = True
    Exit For
  End If
Next ws
If blnNavy = False Then
  Sheets.Add.Name = cstrNavy
End If

With Sheets("nike_data_2022_09")
  For i = 2 To .Range("B" & .Rows.Count).End(xlUp).Row
    If .Range("F" & i) = "Navy" Then
      c = c + 1
      .Range("A" & i).EntireRow.Copy
      Sheets(cstrNavy).Rows.Range("A" & c).PasteSpecial Paste:=xlValues
    End If
  Next i
End With
End Sub

Ciao,
Holger
 
Upvote 0
You were getting the Next Without For error as you were missing an End If for the " If Range("F" & i) = "Navy" Then" line. A good idea is whenever you use an IF/Then or With/End With or loop, indent the "inside text" as I did below. It helps catch these kind of errors.

If I am undertanding what you are trying to do, there may be some other problems. The cose below might be helpful.

VBA Code: 
Sub nikedata()
Dim LastRow%, c%, NavyExists As Boolean
Dim i%, ws As Worksheet

LastRow = Range("B" & Rows.Count).End(xlUp).Row
c = 0
NavyExists = True

For i = 2 To LastRow

    If Range("F" & i) = "Navy" Then
        c = c + 1
        For Each ws In ThisWorkbook.Worksheets
            'If ws.Name = "Navy" Then
            '*** NOT SURE WHY THE [B][I][COLOR=rgb(26, 188, 156)] AND [/COLOR][/I][/B] IS HERE BUT ITS A PROBLEM
            '    [B][I][COLOR=rgb(26, 188, 156)]navyexists = False[/COLOR][/I][/B]
            'End If
100         If ws.Name = "Navy" Then NavyExists = False
        Next ws
    
        '*** if there a logic issue here?
        '*** if the loop above finds a sheet names NAVY, line 100 sets NavyExists = false
        '*** and, if FALSE, line 200 tries to add a sheet named NAVY which will create an error
        
        If NavyExists = False Then
200         Sheets.Add.Name = "Navy"
            Sheets("nike_data_2022_09").Range("F" & i).EntireRow.Copy
            Sheets("Navy").Rows.Range("A" & c).PasteSpecial Paste:=xlValues
        End If
     
        If NavyExists = True Then
            Sheets("nike_data_2022_09").Range("F" & i).EntireRow.Copy
            Sheets("Navy").Rows.Range("A" & c).PasteSpecial Paste:=xlValues
        End If
        
        '*** By correcting Line 100, you can simplify the code above to this ...
        'If NavyExists = False Then Sheets.Add.Name = "Navy"
        'Sheets("nike_data_2022_09").Range("F" & i).EntireRow.Copy
        'Sheets("Navy").Rows.Range("A" & c).PasteSpecial Paste:=xlValues
    
    '*** YOU WERE MISSING THIS "END IF"  That is what was giving you the Next Without For error ***
    End If

Next i

End Sub
 
Upvote 0
Hi joey01,

changed code to clear contents on Sheet Navy if that sheet exists:

VBA Code: 
Sub nikedata_mod()
' https://www.mrexcel.com/board/threads/next-without-for-loop-through-copy-and-paste-data-into-new-sheet.1223621/

Dim c As Long
Dim i As Long
Dim ws As Worksheet
Dim blnNavy As Boolean

Const cstrNavy As String = "Navy"

c = 0

blnNavy = False
For Each ws In ThisWorkbook.Worksheets
  If ws.Name = cstrNavy Then
    blnNavy = True
    Exit For
  End If
Next ws
If blnNavy = False Then
  Sheets.Add.Name = cstrNavy
Else
  Sheets(cstrNavy).UsedRange.ClearContents
End If

With Sheets("nike_data_2022_09")
  For i = 2 To .Range("B" & .Rows.Count).End(xlUp).Row
    If .Range("F" & i) = "Navy" Then
      c = c + 1
      .Range("A" & i).EntireRow.Copy
      Sheets(cstrNavy).Range("A" & c).PasteSpecial Paste:=xlValues
    End If
  Next i
End With
End Sub

Have you considered to use AutoFilter for this task like

VBA Code: 
Public Sub MrE_1223621_1614F13Update()
' https://www.mrexcel.com/board/threads/next-without-for-loop-through-copy-and-paste-data-into-new-sheet.1223621/
Dim ws As Worksheet
Dim blnNavy As Boolean

Const cstrNavy As String = "Navy"

blnNavy = False
For Each ws In ThisWorkbook.Worksheets
  If ws.Name = cstrNavy Then
    blnNavy = True
    Exit For
  End If
Next ws
If blnNavy = False Then
  Sheets.Add.Name = cstrNavy
Else
  Sheets(cstrNavy).UsedRange.ClearContents
End If

With Sheets("nike_data_2022_09")
  If .AutoFilterMode Then .AutoFilterMode = False
  .UsedRange.AutoFilter
  .UsedRange.AutoFilter Field:=6, Criteria1:=cstrNavy
  If .Cells(.Rows.Count, "A").End(xlUp).Row > 1 Then
    .Range("A2", .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, _
        .Cells(1, Columns.Count).End(xlToLeft).Column)).SpecialCells(xlCellTypeVisible).Copy
    Sheets(cstrNavy).Range("A1").PasteSpecial Paste:=xlValues
  End If
  .AutoFilterMode = False
End With
Application.CutCopyMode = False
End Sub

Ciao,
Holger
 
Upvote 0
mmhill said:
You were getting the Next Without For error as you were missing an End If for the " If Range("F" & i) = "Navy" Then" line. A good idea is whenever you use an IF/Then or With/End With or loop, indent the "inside text" as I did below. It helps catch these kind of errors.

If I am undertanding what you are trying to do, there may be some other problems. The cose below might be helpful.

VBA Code: 
Sub nikedata()
Dim LastRow%, c%, NavyExists As Boolean
Dim i%, ws As Worksheet

LastRow = Range("B" & Rows.Count).End(xlUp).Row
c = 0
NavyExists = True

For i = 2 To LastRow

    If Range("F" & i) = "Navy" Then
        c = c + 1
        For Each ws In ThisWorkbook.Worksheets
            'If ws.Name = "Navy" Then
            '*** NOT SURE WHY THE [B][I][COLOR=rgb(26, 188, 156)] AND [/COLOR][/I][/B] IS HERE BUT ITS A PROBLEM
            '    [B][I][COLOR=rgb(26, 188, 156)]navyexists = False[/COLOR][/I][/B]
            'End If
100         If ws.Name = "Navy" Then NavyExists = False
        Next ws
   
        '*** if there a logic issue here?
        '*** if the loop above finds a sheet names NAVY, line 100 sets NavyExists = false
        '*** and, if FALSE, line 200 tries to add a sheet named NAVY which will create an error
       
        If NavyExists = False Then
200         Sheets.Add.Name = "Navy"
            Sheets("nike_data_2022_09").Range("F" & i).EntireRow.Copy
            Sheets("Navy").Rows.Range("A" & c).PasteSpecial Paste:=xlValues
        End If
    
        If NavyExists = True Then
            Sheets("nike_data_2022_09").Range("F" & i).EntireRow.Copy
            Sheets("Navy").Rows.Range("A" & c).PasteSpecial Paste:=xlValues
        End If
       
        '*** By correcting Line 100, you can simplify the code above to this ...
        'If NavyExists = False Then Sheets.Add.Name = "Navy"
        'Sheets("nike_data_2022_09").Range("F" & i).EntireRow.Copy
        'Sheets("Navy").Rows.Range("A" & c).PasteSpecial Paste:=xlValues
   
    '*** YOU WERE MISSING THIS "END IF"  That is what was giving you the Next Without For error ***
    End If

Next i

End Sub
Click to expand...
mmhill said:
You were getting the Next Without For error as you were missing an End If for the " If Range("F" & i) = "Navy" Then" line. A good idea is whenever you use an IF/Then or With/End With or loop, indent the "inside text" as I did below. It helps catch these kind of errors.

If I am undertanding what you are trying to do, there may be some other problems. The cose below might be helpful.

VBA Code: 
Sub nikedata()
Dim LastRow%, c%, NavyExists As Boolean
Dim i%, ws As Worksheet

LastRow = Range("B" & Rows.Count).End(xlUp).Row
c = 0
NavyExists = True

For i = 2 To LastRow

    If Range("F" & i) = "Navy" Then
        c = c + 1
        For Each ws In ThisWorkbook.Worksheets
            'If ws.Name = "Navy" Then
            '*** NOT SURE WHY THE [B][I][COLOR=rgb(26, 188, 156)] AND [/COLOR][/I][/B] IS HERE BUT ITS A PROBLEM
            '    [B][I][COLOR=rgb(26, 188, 156)]navyexists = False[/COLOR][/I][/B]
            'End If
100         If ws.Name = "Navy" Then NavyExists = False
        Next ws
   
        '*** if there a logic issue here?
        '*** if the loop above finds a sheet names NAVY, line 100 sets NavyExists = false
        '*** and, if FALSE, line 200 tries to add a sheet named NAVY which will create an error
       
        If NavyExists = False Then
200         Sheets.Add.Name = "Navy"
            Sheets("nike_data_2022_09").Range("F" & i).EntireRow.Copy
            Sheets("Navy").Rows.Range("A" & c).PasteSpecial Paste:=xlValues
        End If
    
        If NavyExists = True Then
            Sheets("nike_data_2022_09").Range("F" & i).EntireRow.Copy
            Sheets("Navy").Rows.Range("A" & c).PasteSpecial Paste:=xlValues
        End If
       
        '*** By correcting Line 100, you can simplify the code above to this ...
        'If NavyExists = False Then Sheets.Add.Name = "Navy"
        'Sheets("nike_data_2022_09").Range("F" & i).EntireRow.Copy
        'Sheets("Navy").Rows.Range("A" & c).PasteSpecial Paste:=xlValues
   
    '*** YOU WERE MISSING THIS "END IF"  That is what was giving you the Next Without For error ***
    End If

Next i

End Sub
Click to expand...
Tyvm boss this makes sense, will definitely start indenting
 
Upvote 0
HaHoBe said:
Hi joey01,

maybe

VBA Code: 
Sub nikedata()
' https://www.mrexcel.com/board/threads/next-without-for-loop-through-copy-and-paste-data-into-new-sheet.1223621/

Dim c As Long
Dim i As Long
Dim ws As Worksheet
Dim blnNavy As Boolean

Const cstrNavy As String = "Navy"

c = 0

blnNavy = False
For Each ws In ThisWorkbook.Worksheets
  If ws.Name = cstrNavy Then
    blnNavy = True
    Exit For
  End If
Next ws
If blnNavy = False Then
  Sheets.Add.Name = cstrNavy
End If

With Sheets("nike_data_2022_09")
  For i = 2 To .Range("B" & .Rows.Count).End(xlUp).Row
    If .Range("F" & i) = "Navy" Then
      c = c + 1
      .Range("A" & i).EntireRow.Copy
      Sheets(cstrNavy).Rows.Range("A" & c).PasteSpecial Paste:=xlValues
    End If
  Next i
End With
End Sub

Ciao,
Holger
Click to expand...
This code works perfectly and is much cleaner thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Copy paste columns based on matching header into another workbook
Replies
5
Views
327
mumps
mumps
M
  • Solved
cut/copy and paste row based on cell value not being equal to specific text
Replies
6
Views
626
mlan1604
M
G
  • Question Question
Disable Cut Copy Paste
Replies
1
Views
899
jkpieterse
jkpieterse
V
  • Question Question
Copy Range from Multiple Sheets and Paste Link into a Single Sheet
Replies
2
Views
341
vbascientist
V
A
  • Question Question
VBA Copy/Paste New Data Only
Replies
0
Views
446
ah2024_2024
A

Forum statistics

Threads
1,224,828
Messages
6,181,213
Members
453,024
Latest member
Wingit77

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top