Need Help Making a Macro Repeat Until All Rows Are Processed

[jessiright77]

Hello.

I have a spreadsheet that contains many records... one record per row.
When I place my cursor in one of the rows, I can invoke a macro that
prints a barcode sheet for the ONE record in that row.

However... now I need to print a barcode sheet for ALL of the records
(and there are hundreds of them). Is there any way to modify the
following macro so that it will print a barcode sheet for ALL of the
records in one batch (instead of my having to manually run the macro
for each row)?

Any help would be greatly appreciated! Thanks,
Jessi


THE MACRO:

Sub PrintBarcode()

'Dimension the variables:
Dim iOffsetValue As Integer

'Get the row number and save to the iOffsetValue variable (you must
subtract one to account for the header row):
iOffsetValue = ActiveWindow.ActiveCell.Row - 1
'Make the barcode sheet visible:
Sheets("BarcodeSheet").Visible = True
'Select the barcode sheet:
Sheets("BarcodeSheet").Select
'Select the offset adjustment cell:
Range("M2").Select
'Place the offset value:
ActiveCell.FormulaR1C1 = iOffsetValue

'Print and hide the barcode sheet:
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("BarcodeSheet").Visible = False
'ActiveWindow.SelectedSheets.Visible = False
End Sub

 

[jessiright77]

Thanks for your help, John, but when I modified and ran the macro,
however, I got the following error mesage:
"Run-time Error '1004': Application-defined or object-defined error."

I have reinserted the modified code. Did I do it wrong?

Thanks,
Jessi

Sub PrintBarcode()

'Dimension the variables:
Dim iOffsetValue As Integer

'Get the row number and save to the iOffsetValue variable (you must
subtract one to account for the header row):
iOffsetValue = ActiveWindow.ActiveCell.Row - 1
'Make the barcode sheet visible:
Sheets("BarcodeSheet").Visible = True
Do Until Cells(iOffsetValue, 1) = "" 'place this after you make the
sheet visible unless there are multiple sheets
'Select the barcode sheet:
Sheets("BarcodeSheet").Select
'Select the offset adjustment cell:
Range("M2").Select
'Place the offset value:
ActiveCell.FormulaR1C1 = iOffsetValue

'Print and hide the barcode sheet:
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'Increment iOffsetValue:
iOffsetValue = iOffsetValue + 1
Loop
Sheets("BarcodeSheet").Visible = False
'ActiveWindow.SelectedSheets.Visible = False
End Sub

 

[jessiright77]

Thank you for your help, John, but it is still printing only the one
record for the row where my cursor is sitting.

I have reinserted the modified code... did I do it wrong?

Thanks,
Jessi

Sub PrintBarcode()

'Dimension the variables:
Dim iOffsetValue As Integer

'Get the row number and save to the iOffsetValue variable (you must
subtract one to account for the header row):
iOffsetValue = ActiveWindow.ActiveCell.Row - 1
'Make the barcode sheet visible:
Sheets("BarcodeSheet").Visible = True

Do Until Cells(iOffsetValue, 1) = "" 'place this after you make the
sheet visible unless there are multiple sheets
'Select the barcode sheet:
Sheets("BarcodeSheet").Select
'Select the offset adjustment cell:
Range("M2").Select
'Place the offset value:
ActiveCell.FormulaR1C1 = iOffsetValue

'Print and hide the barcode sheet:
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'Increment iOffsetValue:
iOffsetValue = iOffsetValue + 1

Loop

Sheets("BarcodeSheet").Visible = False
'ActiveWindow.SelectedSheets.Visible = False

End Sub

 

[jessiright77]

Thank you for your help, John, but it is still printing only the one
record for the row where my cursor is sitting.

I have reinserted the modified code... did I do it wrong?

Thanks,
Jessi

Sub PrintBarcode()

'Dimension the variables:
Dim iOffsetValue As Integer

'Get the row number and save to the iOffsetValue variable (you must
subtract one to account for the header row):
iOffsetValue = ActiveWindow.ActiveCell.Row - 1
'Make the barcode sheet visible:
Sheets("BarcodeSheet").Visible = True

Do Until Cells(iOffsetValue, 1) = "" 'place this after you make the
sheet visible unless there are multiple sheets
'Select the barcode sheet:
Sheets("BarcodeSheet").Select
'Select the offset adjustment cell:
Range("M2").Select
'Place the offset value:
ActiveCell.FormulaR1C1 = iOffsetValue

'Print and hide the barcode sheet:
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'Increment iOffsetValue:
iOffsetValue = iOffsetValue + 1

Loop

Sheets("BarcodeSheet").Visible = False
'ActiveWindow.SelectedSheets.Visible = False

End Sub

 

[Guest]

Sorry I couldn't get back to you sooner, I work odd days and hours. I'm glad
you got it to work. The reason it did that is because you switch to the
barcode sheet near the end of the code, when it loops it tries to rund the
macro on the current page which is wrong, you have to go back and select your
original "Data" sheet for it to start the process over, I didn't catch that
when i replied, good job not giving up though! It's hard enough to do this
stuff but even harder when you don't have all of the data to test with.

-John