Continue Macro until list of data is done

[kdsteff]

Hi! How can I keep a macro running to the end of the data in a column on
one worksheet? I don't want to spend hours copying and pasting one row in
this column of data to another worksheet that performs calculations and then
copy and paste the output to a different worksheet, and then doing it over
again, all with one Macro. Make sense?

Thanks much!

 

[Bob Phillips]

Here's the basic structure

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow 'LastRow to 1 Step -1 to bottom-up

Next i

End With

End Sub

 

[Gord Dibben]

One method............

Dim rng As Range
Dim rngTocopy As Range

Set rngTocopy = Range("A1", Cells(Rows.Count, "A").End(xlUp))
For Each rng In rngTocopy

'do things which you did not detail very well

Next rng

Another one..................
Dim iSource As Long

iSource = 1

Do

'do things that are mostly unknown currently

Loop Until IsEmpty(Cells(iSource, "A").Value)


Gord Dibben MS Excel MVP

 

[kdsteff]

Thanks for the help! A couple more questions. Loop is running but it is
getting hung up on the application run of another macro "BalanceMeatCost."

Also, it is not moving down a cell in the sheet "Flock Costs."

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 8/6/2008 by KDSteffenhagen
'

'
Dim iSource As Long

iSource = 1

Do

Sheets("Flock Costs").Select
ActiveWindow.LargeScroll ToRight:=-2
ActiveWindow.SmallScroll Down:=-3
Range("A2").Select
Selection.Copy
Sheets("Meat cost by flock").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Run "'Meat Cost by Flock - Period 8.xls'!BalanceMeatCost"
Sheets("Meat cost by flock").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary Report").Select
Range("A8").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("B8").Select
Sheets("Meat cost by flock").Select
Range("C6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary Report").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C8").Select
Sheets("Meat cost by flock").Select
Range("C7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary Report").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("D8").Select
Sheets("Meat cost by flock").Select
Range("C11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary Report").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("E8").Select
Sheets("Meat cost by flock").Select
Range("C12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary Report").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("F8").Select
Sheets("Meat cost by flock").Select
Range("C13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary Report").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("G8").Select
Sheets("Meat cost by flock").Select
Range("D23").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary Report").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("H8").Select
Sheets("Meat cost by flock").Select
ActiveWindow.SmallScroll Down:=6
Range("E41").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary Report").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("I8").Select
Sheets("Meat cost by flock").Select
ActiveWindow.SmallScroll Down:=-15
Range("H5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary Report").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("J8").Select
Sheets("Meat cost by flock").Select
ActiveWindow.SmallScroll Down:=-6
Range("K5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary Report").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Rows("9:9").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Sheets("Flock Costs").Select
Range("A3").Select
Selection.Copy
Sheets("Meat cost by flock").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub

 

[kdsteff]

We went with this one. Works better. But what is code to end the macro when
it reaches a blank cell please?

' Keyboard Shortcut: Ctrl+g
'
Dim rng As Range
Dim rngTocopy As Range

Set rngTocopy = Range("A1", Cells(Rows.Count, "A").End(xlUp))
For Each rng In rngTocopy
Application.Goto Reference:="printstart"
Selection.End(xlDown).Select
Selection.Copy
Application.Goto Reference:="flocknumber"
ActiveSheet.Paste
Application.Run "'Meat Cost by Flock - Period 8.xls'!BalanceMeatCost"
Application.Goto Reference:="printstart"
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="printstart"
Selection.End(xlDown).Select
Application.CutCopyMode = False
Selection.ClearContents
Next rng

End Sub

 

[Dave Peterson]

Your code looks at all the cells between A1 and the last used cell in column A.

If you have empty cells, do you really want the code to quit--or just ignore
that empty cell.

If you want it to quit:

For Each rng In rngTocopy

if rng.value = "" then exit for '<-- add this line

Application.Goto Reference:="printstart"

But it seems kind of strange to me.

 

[kdsteff]

The macro gets stuck on the last blank cell and keeps running on that cell.
Have to manually quit it.

 

[Dave Peterson]

I don't see how that happens based on your posted code.

But that "exit for" line should quit the loop.

Are you saying it worked after this suggestion or it still didn't work?

If it didn't work, post your current code.

 

[Gord Dibben]

The macro will work through the cells(rng) in rngTocopy using the For..Next

When all cells in rngTocopy have been processed the macro will stop.

I don't understand what you're doing but looks like you are copying and
pasting the same range until the macro reaches last filled cell in Column A
on the activesheet.

But you clear contents after each copy................I assume your called
macro must populate that range for the next copy/paste


Gord