Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Making a Macro Repeat Until All Rows Are Processed
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Making a Macro Repeat Until All Rows Are Processed
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Making a Macro Repeat Until All Rows Are Processed
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Making a Macro Repeat Until All Rows Are Processed
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Making a Macro Repeat Until All Rows Are Processed
Well... I kept playing with this. I was finally able to make it work
if I inserted the following "selection" commands before the End Loop command. ..... 'Select cell A1 of the Data worksheet (to restart back to the beginning of the loop): Sheets("Data").Select Range("A1").Select Loop I'm not sure why this works... why would you need to select the data worksheet in order to restart the macro? Thanks, Jessi |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Making a Macro Repeat Until All Rows Are Processed
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 " wrote: Well... I kept playing with this. I was finally able to make it work if I inserted the following "selection" commands before the End Loop command. ..... 'Select cell A1 of the Data worksheet (to restart back to the beginning of the loop): Sheets("Data").Select Range("A1").Select Loop I'm not sure why this works... why would you need to select the data worksheet in order to restart the macro? Thanks, Jessi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
repeat two rows macro please | New Users to Excel | |||
need macro :repeat rows at top | New Users to Excel | |||
Display current sheet name being processed in macro | New Users to Excel | |||
repeat macro for all rows | Excel Programming | |||
Foreign characters aren't processed in a macro | Excel Programming |