Looking to simplify my VBA

Question

One of my assignments is creating a large output whereby I take screen generated by a program, format the output, then cut/paste as print screen into PowerPoint. I wrote the following:

Range("B6:M6").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.PageSetup.PrintArea = "$B$6:$M$300"
Set ActiveSheet.HPageBreaks(1).Location = Range("B16")
Set ActiveSheet.HPageBreaks(2).Location = Range("B26")
Set ActiveSheet.HPageBreaks(3).Location = Range("B36")
Set ActiveSheet.HPageBreaks(4).Location = Range("B46")
Set ActiveSheet.HPageBreaks(5).Location = Range("B56")
Set ActiveSheet.HPageBreaks(6).Location = Range("B66")

...and so on every ten rows. Then I made it so it would cut and paste each print area page by page, and drop it on the end sheet. That way I can easily transfer each to .ppt. Eventually I want to learn enough to automate the entire process, but gotta take it in steps. The cut/paste/print looks like this:

Range("B6:M15").Select
Selection.CopyPicture Appearance:=xlPrinter, Format:=xlPicture
Sheets("Sheet2").Select
ActiveSheet.Paste
ActiveSheet.Shapes.Range(Array("Picture 1")).Select
Selection.ShapeRange.Width = 719.28

Sheets("Private Company (w Debt)").Select
Range("B16:M25").Select
Selection.CopyPicture Appearance:=xlPrinter, Format:=xlPicture
Sheets("Sheet2").Select
ActiveSheet.Paste
ActiveSheet.Shapes.Range(Array("Picture 2")).Select
Selection.ShapeRange.Width = 719.28

Sheets("Private Company (w Debt)").Select
Range("B26:M35").Select
Selection.CopyPicture Appearance:=xlPrinter, Format:=xlPicture
Sheets("Sheet2").Select
ActiveSheet.Paste
ActiveSheet.Shapes.Range(Array("Picture 3")).Select
Selection.ShapeRange.Width = 719.28

In my macro, the code follows the page breaks directly (it's like 40 pages or so), it runs decently well.

Is there anyone who can show me how you might write this more intuitively so VBA knows to set a horizontal page break every ten rows, then print cut/paste without physically writing out each line and specifying the exact cells?

Study up on variables? Edit: see [this](http://stackoverflow.com/questions/30378320/excel-vba-loop-through-range). — findwindow, Nov 20 '15 at 16:19
Perhaps this is better for [CodeReview](http://codereview.stackexchange.com/) instead of SO. Also, it would greatly help to learn [how to avoid `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). Use the Macro Recorder to do some easy, medium, and "hard" things in Excel and review the code to see how it works. It looks like you just need to take some time to learn VB, but it will *greatly* help to kick-start your understanding by just learning how to remove `.Select`, since you'll learn how to work directly with the data, and not selections — BruceWayne, Nov 20 '15 at 16:24
look into loops. `for i = 6 to 295 step 10` will loop every ten where i will reprensent the row number. Also look into the resize, this will allow you to use the first cell Range("B" & i).resize(10,13) to set your area to copy as image. — Scott Craner, Nov 20 '15 at 16:28
Thanks so much, guys! I'll ask on code review and will now start reviewing your suggestions. Really appreciate it! — frankslivingmodel, Nov 20 '15 at 16:29
Just FYI too, [Chip Pearson's site](http://www.cpearson.com/Excel/Topic.aspx) is a ridiculously great resource. — BruceWayne, Nov 20 '15 at 17:07

score 0 · Answer 1 · answered Nov 20 '15 at 16:32

Hmmm something like:

Dim i as Long
Dim copyRange as Range
Range("B6:M6").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.PageSetup.PrintArea = "$B$6:$M$300"


For i = 1 to 6   '## Modify from 6 to a larger number, as needed
    'Set up your page break locations
    Set ActiveSheet.HPageBreaks(i).Location = Range("B" & 6 + (10 * i))
    'copy/paste in to Sheet2:
    ' use the resize method to get a 10 rows x 12 columns range
    Set copyRange = ActiveSheet.HPageBreaks(i).Location.Resize(10, 12)
    'copyPicture:
    copyRange.CopyPicture Appearance:=xlPrinter, Format:=xlPicture

    With Workshheets("Sheet2")
        .Paste
        .Shapes.Range(Array("Picture " & i)).ShapeRange.Width = 719.28
    End With

Next

Looking to simplify my VBA

1 Answers1