VBA-Loop Help

[hlpgilbert]

Good Evening,

I have Created the below macro, however I cannot figure out how to make it loop so that is continues to copy the next column over ( so go from column E:E , To Column F:F, and then To Column G:G), and paste the data into the same column (D:D or Range paste_range) everytime, and print.
If anyone can help me it will be much appreciated.

Sub paster_print()
'
' paster_print Macro
'
'
Columns("E:E").Select
Selection.Copy
Application.Goto Reference:="paste_range"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="Print_Area"
Application.CutCopyMode = False
End Sub

 

[JoeMo]

maybe like this

For i = 5 to 7
    Cells(1,i).EntireColumn.Copy
        Application.Goto Reference:="paste_range"
        Selection.PasteSpecial Paste:=xlPasteValues
        Application.Goto Reference:="Print_Area"
'add a print line if you want to print now, otherwise delete the line above this 'one
        Application.CutCopyMode = False
Next i

 

[AlphaFrog]

You could have it print each column without the need to copy and paste. This prints each column one-at-a-time from columns E to J

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Print_Each_Column()<br>    <br>    <SPAN style="color:#00007F">Dim</SPAN> col <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> col <SPAN style="color:#00007F">In</SPAN> Range("E:J").Columns<br>        ActiveSheet.PageSetup.PrintArea = col.Address<br>        ActiveWindow.SelectedSheets.PrintOut Copies:=1<br>    <SPAN style="color:#00007F">Next</SPAN> col<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[hlpgilbert]

Thank you , it worked perfectly.

 

[AlphaFrog]

You're welcome.

Duplicate question:
http://www.mrexcel.com/forum/showthread.php?t=581150

 

[Rick Rothstein]

ActiveWindow.SelectedSheets.PrintOut Copies:=1

I haven't done much printing via code, so I have a question... Why did you use the ActiveWIndows.SelectedSheets reference instead of just using an ActiveSheet reference? Your code using this statement in place of the one you posted appears to work the same way...

ActiveSheet.PrintOut Copies:=1

 

[AlphaFrog]

I recorded the "print" function to get the syntax and didn't clean up the recorded code. ActiveWindow.SelectedSheets is what was recorded, but I would use ActiveSheet as well if I had taken the time to clean it up. I noticed it after I had posted.

 

[Rick Rothstein]

I recorded the "print" function to get the syntax and didn't clean up the recorded code. ActiveWindow.SelectedSheets is what was recorded, but I would use ActiveSheet as well if I had taken the time to clean it up. I noticed it after I had posted.

Thanks... I just wanted to make sure I wasn't missing something subtle here.