Help understanding this Macro please

[Pizgatti]

HI, I am sure it is simple but I know nothing of macro editing although I recorded this one and simply need it to repeat on each consecutive row to line 228 so its range is set at

A18:J18 I then need it to run on A19:J19 then so on till A228:J228 I would greatly appreciate any education on how to do that


</pre>
' Macro recorded 4/7/2014 by Phoenix-TW
'
' Keyboard Shortcut: Ctrl+r
'
Range("A18:J18").Select
Selection.Copy
Sheets("tro7880_384003666_94B9055DXA122").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Sheet1").Select

End Sub</pre>

 

[bbott]

Hello,

If you're simply copying and pasting each line, why not just copy and paste the entire range all together? Replace:

Range("A18:J18").Select

with

Range("A18:J228").Select

 

[Pizgatti]

Thank you but not sure that would work if it copied all the rows at once, each line is pasted on to another page into a formula then printed,
each row is for a separate customer it needs to be copied on its own, there row is then pasted in that formula and then printed. Only one row of information is needed at a time.

 

[Smitty]

Welcome to the Board!

See how this works for you:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wsSource <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> wsDest <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>        <SPAN style="color:#00007F">Set</SPAN> wsSource = Sheets("Sheet1")<br>        <SPAN style="color:#00007F">Set</SPAN> wsDest = Sheets("tro7880_384003666_94B9055DXA122")<br>        <br>        lr = wsSource.Cells(Rows.Count, "A").End(xlUp).Row<br>        <br>        <SPAN style="color:#00007F">For</SPAN> i = 18 <SPAN style="color:#00007F">To</SPAN> lr<br>            wsSource.Range("A" & i & ":J" & i).Copy wsDest.Range("A1")<br>            wsDest.PrintPreview<br>            <SPAN style="color:#007F00">'   Uncomment after testing</SPAN><br>            <SPAN style="color:#007F00">'wsdest.printout</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[Pizgatti]

Thanks for all the help, it still did not end up working (probably from something I was doing wrong) but someone that knows about it ended up coming by and writing this if it helps any one else,
Sub form()
Sheets("Sheet1").Select
' Find the last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).row

' Loop through each row

Dim x As Integer
Dim i As Integer

For x = 18 To FinalRow
'Select first row
Worksheets("sheet1").Cells(x, 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

'Paste in new sheet
Sheets("tro7880_384003666_94B9055DXA122").Select
Cells(4, 2).Select
ActiveSheet.Paste
'Print
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Sheets("Sheet1").Select


Next x
End Sub

 

[Smitty]

Just note that you don't need to Select, and it's usually unnecessary, inefficient, and just slows things down.

And it seems that the only difference it the destination location: Cells(4, 2).Select

So you could change my code:

wsSource.Range("A" & i & ":J" & i).Copy wsDest.Cells(4,2)

Or

wsSource.Range("A" & i & ":J" & i).Copy wsDest.Range("B4")