infinite print loop help

5SRopp

New Member
Joined
Sep 22, 2010
Messages
29
I have a range of data A2:E16 in which each row needs to be copied and pasted into another worksheet cell by cell. After this the worksheet needs to be printed, this needs to repeat for each row in the range. I recorded a macro for the action I want repeated, when I tried a For loop I entered an infinite loop of printing the same thing. So my loop didn't cycle row by row through the range and it never stopped. I'm using VB6.5 and excel 2007. This is a sample range of the entire data, it will take days to print these one by one without a macro. Your help will be greatly appreciated. Below is the macro I've been working with
' quickprint Macro
'
' Keyboard Shortcut: Ctrl+h
'
Dim i As Variant
Sub Cell_Loop()
For Each i In Worksheets("Modified").Range("A2:E16").Cells

Range("A2").Select
Selection.Copy
Sheets("Sign").Select
Range("D21:P31").Select
ActiveSheet.Paste
Range("W21:AI31").Select
ActiveSheet.Paste
Sheets("Modified").Select
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sign").Select
Range("D2:P17").Select
ActiveSheet.Paste
Range("W2:AI17").Select
ActiveSheet.Paste
Sheets("Modified").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sign").Select
Range("D18:P20").Select
ActiveSheet.Paste
Range("W18:AI20").Select
ActiveSheet.Paste
Sheets("Modified").Select
Range("D2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sign").Select
Range("E32:H36").Select
ActiveSheet.Paste
Range("X32:AA36").Select
ActiveSheet.Paste
Sheets("Modified").Select
Range("E2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sign").Select
Range("M32:P36").Select
ActiveSheet.Paste
Range("AF32:AI36").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Next i
End Sub

End Sub
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Welcome to the forums!

Not sure exactly what you're trying to do with this, but hopefully this helps. Cleaned up the code, fixed what I think was the error, and made it run faster in general:

Code:
Sub Cell_Loop()
Application.ScreenUpdating = False
For Each i In Worksheets("Modified").Range("A2:E16")
    Sheets("Modified").Range("A2").Copy Destination:=Sheets("Sign").Range("D21:P31, W21:AI31")
    Sheets("Modified").Range("B2").Copy Destination:=Sheets("Sign").Range("D2:P17, W2:AI17")
    Sheets("Modified").Range("C2").Copy Destination:=Sheets("Sign").Range("D18:P20, W18:AI20")
    Sheets("Modified").Range("D2").Copy Destination:=Sheets("Sign").Range("E32:H36, X32:AA36")
    Sheets("Modified").Range("E2").Copy Destination:=Sheets("Sign").Range("M32:P36,AF32:AI36")
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Next i
Application.ScreenUpdating = True
End Sub
 

5SRopp

New Member
Joined
Sep 22, 2010
Messages
29
The macro is supposed to take each row in the range and copy and paste the data cell by cell into "Sign". This is a sign template, so after the data is copied and pasted into the sign it should print. This process needs to repeat for the next row of data in the range. Each row of data is different. My code makes more sense now that you cleaned it up, thanks!
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
This should do exactly what you need. Modified it so that you aren't looping too many times.

Code:
Public Sub CopyLoop()
Application.ScreenUpdating = False
Dim i As Long
For i = 2 To 16
    Sheets("Modified").Range("A" & i).Copy Destination:=Sheets("Sign").Range("D21:P31, W21:AI31")
    Sheets("Modified").Range("B" & i).Copy Destination:=Sheets("Sign").Range("D2:P17, W2:AI17")
    Sheets("Modified").Range("C" & i).Copy Destination:=Sheets("Sign").Range("D18:P20, W18:AI20")
    Sheets("Modified").Range("D" & i).Copy Destination:=Sheets("Sign").Range("E32:H36, X32:AA36")
    Sheets("Modified").Range("E" & i).Copy Destination:=Sheets("Sign").Range("M32:P36,AF32:AI36")
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Next i
Application.ScreenUpdating = True
End Sub
 

Forum statistics

Threads
1,143,654
Messages
5,720,098
Members
422,266
Latest member
Mattyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top