How to repeat a code using the value of the next cell until blank

Question

I am very new to Excel VBA coding and have been having a hard time trying to figure out a code that will let me repeat my code on the next row until it comes blank.

I am trying to get the next value in the data worksheet after cell A7, so it is A8 and repeat the macro until I reach a blank cell.

Sheets("data").Select
Range("A7").Select
Selection.Copy
Sheets("CODING BLOCK").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Sheets("CODING BLOCK").Select
Application.CutCopyMode = False
Sheets("CODING BLOCK").Copy Before:=Sheets(1)
ActiveSheet.Select
ActiveSheet.Name = ActiveSheet.Range("L1")

Any guidance in the right direction would be greatly appreciated.

You need a ```for loop```, but does this code actually run? What happens if you click the first cell in ```sheets("CODING BLOCK")``` before you run this, because nowhere are you specifying to place any text in ```L1``` and if it is just a value that is always there then this will fail on the second iteration of the loop due to a name conflict. — Warcupine, Aug 29 '19 at 19:26
Side note: You want to avoid [using activate and select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. — cybernetic.nomad, Aug 29 '19 at 19:43
That was the problem I was having. I would take the value from the data sheet, paste it in cell L1, duplicate the sheet, then repeat with the next value in the next row in the data sheet. I did keep getting an duplicate sheet which is how I got stuck =o( — amy263587, Aug 29 '19 at 20:40

Wilco · Accepted Answer · 2019-08-29T21:02:56.230

0

You need a loop to iterate over each row. Something like this may help you get started:

Sub test()
Dim LastRow As LongPtr  'LongPtr is compatible with 32 and 64 bit machines
Dim CurrRow As LongPtr 


CurrRow = 7  '<-- set your current row

' find the last non-empty cell in column A
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

' MsgBox LastRow <-- un-comment to print out last row

' loop over rows
Do While CurrRow <= LastRow
    ' your code here
    Sheets("data").Select
    Range("A" & CurrRow).Select  'use the CurrRow variable to select the correct cell
    Selection.Copy
    Sheets("CODING BLOCK").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("CODING BLOCK").Select
    Application.CutCopyMode = False
    Sheets("CODING BLOCK").Copy Before:=Sheets(1)
    ' ActiveSheet.Select 
    ' ActiveSheet.Name = ActiveSheet.Range("L1") These two lines might be causing your blank sheet issue
    CurrRow = CurrRow + 1  'increment the CurrRow variable
Loop


End Sub

debug statements are your friend. I use MsgBox because it creates a popup that pauses execution, but debug.print works well too if you're ok with using the "Immediate" window.

edited Aug 29 '19 at 21:02

answered Aug 29 '19 at 19:31

Wilco

374
1
11

this actually worked perfectly! However there was an error at the end and it created a duplicate sheet with a blank value. – amy263587 Aug 29 '19 at 20:53
hmm it might be the last two lines before the CurrRow Increment. I edited my answer to comment them out because unless I'm missing something they are extraneous anyhow. – Wilco Aug 29 '19 at 21:03
@amy263587 also if this solved your problem could mark my answer as solution so I can get some of that sweet sweet reputation :D – Wilco Aug 29 '19 at 21:25
Unfortunately, f I remove these two lines, my sheet does not get renamed. Should I add a second sub set to rename the sheets? – amy263587 Aug 29 '19 at 22:35
It worked like a charm when I removed the two lines referenced – amy263587 Aug 29 '19 at 22:58

How to repeat a code using the value of the next cell until blank

1 Answers1