Do Until Loop Excel VBA

Question

I am trying to create "Do Until" loop in Excel VBA where it copies each value populated in column A of "SQL" worksheet (starting in cell A2), pastes the value into cell "A2" of the "Home" worksheet, runs the "PDF" macro that already exists, continues this process for each populated value, and ends when there are no more values in column A of "SQL" worksheet. I'm very new to VBA, and tried coming with something from other posts/blog. Any help would be greatly appreciated.

Dim rngMyRange As Range, rngCell As Range
Dim sht As Worksheet
Dim LastRow As Long
Dim SheetName As String


With Worksheets("SQL")
Set rngMyRange = .Range(.Range("a2"), .Range("A1000").End(x1up))

Do Until IsEmpty(Cells(rowNo, 1))
  For Each rngCell In rngMyRange
  rngCell.Cells.Select
  Selection.Copy
  Sheets("HOME").Select
  Range("A2").Select
  ActiveSheet.Paste
  Application.CutCopyMode = False
  Application.Run "'PDF Generator.xlsm'!PDF"

Loop
End Sub

So what's the problem? There is no question in this post. Please read [ask]. — Mathieu Guindon, Aug 25 '17 at 17:40

Scott Craner · Answer 1 · 2017-08-29T17:55:35.347

2

You have a for loop inside the Do loop. Remove the Do loop.

You never use End With

Also avoid the use of .Select and .Activate it slows the code:

Dim rngMyRange As Range, rngCell As Range


With Worksheets("SQL")
    Set rngMyRange = .Range(.Range("a2"), .Range("A1048576").End(xlup))
End With

For Each rngCell In rngMyRange
  rngCell.Copy
  Sheets("HOME").Range("A2").PasteSpecial
  Application.CutCopyMode = False
  Application.Run "'PDF Generator.xlsm'!PDF"
Next rngCell

edited Aug 29 '17 at 17:55

answered Aug 25 '17 at 17:35

Scott Craner

148,073
10
49
81

Hi Scott - Thank you for your help! I'm wondering if you may know a better way for me to define the range. I'm getting an application defined or object defined error. My range will vary day to day, and it will be based on the number of populated rows in column A of the "SQL" worksheet, beginning in cell "A2". – SamL Aug 28 '17 at 18:09
On which line are you getting the error? also see edit I made it look beyond the 1000th row. – Scott Craner Aug 28 '17 at 18:11
It points to the whole range line when I debug. Set rngMyRange = .Range(.Range("a2"), .Range("A1048576").End(x1up)) – SamL Aug 29 '17 at 17:54
Missed you had a typo on `x1up` should be `xlup` – Scott Craner Aug 29 '17 at 17:55
Thank you so much for your help! It works perfectly! – SamL Aug 30 '17 at 16:53

score 1 · Answer 2 · answered Aug 25 '17 at 17:32

1

Add a dot here:

Do Until IsEmpty(.Cells(rowNo, 1))

Without the dot it is refering to the activesheet.

answered Aug 25 '17 at 17:32

Vityata

42,633
8
55
100

Do Until Loop Excel VBA

2 Answers2

Linked