Excel VBA - Not looping through my while loop

Question

So I have a code thats pasting data into an excel column, i'm then trying to loop through the data in that column and create a new sheet from data for every value in column B but it's stopping after completing this action once and not looping through the column.

Any ideas?

  i = 4

Do While Cells(i, 2).Value <> ""
    Worksheets("Front").Cells(5, 3).Value = Cells(i, 2)
    Worksheets("Front").Select
    Range("C2:M35").Select
    Selection.Copy
    Sheets("PlaceHolder").Select
    Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Dim wks As Worksheet
    Set wks = ActiveSheet
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)
    ActiveSheet.Name = wks.Range("C5").Value
    i = i + 1
Loop

A cell is probably blank, it is also using the active sheet which I think would be the new sheet after it is created. — Warcupine, Apr 09 '21 at 15:34

score 0 · Accepted Answer · answered Apr 09 '21 at 17:21

"Cells" and "Range" in your code both refer to the ActiveWorksheet, which presumably starts out as Worksheets("Front"), then you change ActiveWorksheet to Worksheets("Placeholder") with the Worksheets("Placeholder").Select statement. I'm not sure if creating the new worksheet sets ActiveWorksheet to the new worksheet or not and I shouldn't have to know to make the code work. Instead of relying on the implicit reference to ActiveWorksheet, you should use explicit references like this.

'Style note: Always put Option Explict at the top of the module
'and declare all your variables at the top of the subroutine. 
Dim wsFront As Worksheet
Dim wsPlaceholder As Worksheet
Dim wsNewSheet As Worksheet
Dim i As Integer

Set wsFront = Worksheets("Front")
Set wsPlaceholder = Worksheets("Placeholder")

i = 4

Do While wsFront.Cells(i, 2).Value <> ""

    ' Copy data from Worksheets("Front")
    wsFront.Cells(5, 3).Value = Cells(i, 2)
    wsFront.Select
    wsFront.Range("C2:M35").Select
    Selection.Copy
    
    ' Paste data to Worksheets("Placeholder")
    wsPlaceholder.Select
    wsPlaceholder.Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    
    ' Copy Worksheets("Placeholder") to new worksheet
    wsPlaceholder.Copy After:=Worksheets(Sheets.Count)
    
    'Get a reference to the new worksheet
    Set wsNewSheet = Worksheets(Worksheets.Count)
    wsNewSheet.Name = wsPlaceholder.Range("C5").Value
    i = i + 1
Loop

score 0 · Answer 2 · answered Apr 09 '21 at 17:22

There's typically no need to select or activate anything when using VBA in Excel (despite what the macro recorder might try to suggest)

If you plan on doing much VBA work in Excel then this is highly-recommended reading: How to avoid using Select in Excel VBA

Dim wsData As Worksheet, wsFront As Worksheet, wsPH As Worksheet, v
Dim wb As Workbook, i As Long

Set wb = ThisWorkbook
Set wsData = ActiveSheet 'or some other specific sheet
Set wsFront = wb.Worksheets("Front")
Set wsPH = wb.Worksheets("PlaceHolder")

i = 4

Do While wsData.Cells(i, 2).Value <> ""
    v = wsData.Cells(i, 2).Value
    wsFront.Cells(5, 3).Value = v
    
    wsFront.Range("C2:M35").Copy
    With wsPH.Range("C2")
        .PasteSpecial Paste:=xlPasteValues
        .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    End With
    
    wsPH.Copy After:=wb.Worksheets(Sheets.Count)
    wb.Worksheets(Sheets.Count).Name = v
Loop

Excel VBA - Not looping through my while loop

2 Answers2