How do I convert the "A1:F47" range in code

[missouridawg]

to formulas?

I have a worksheet in which I want to make 300 copies of the same exact page and simply paste them below each other. Rows 1 through 47 (columns A through F) is exactly 1 page of data when printed on 8.5" x 11" paper. I want to write a script that copies that first page and pastes it directly below itself.

Page 2 would start on row 48. (1*47+1)
Page 3 would start on row 95 (2*47+1)
Page 4 would start on row 142 (3*47+1)
.
.
.
Page 300 would start on row 14101 (300*47+1)


While I am familiar with FOR loops and can set those up to do the counting in this kind of scenario, I'm having issues with converting a cell locator reference to a countable/iterative entity.

The code I'm pasting below selects the first page and copies it to the second page. How do I convert the ("A1:F47") portion of that code into using variables that can be iterated against in a FOR loop?

Range("A1:F47").Select
Selection.Copy
Range("A48").Select
ActiveSheet.Paste

 

[JoeMo]

Assuming you just want to copy the values in A1:F47 300 times:

Sub ThreeHundredTimes()
Dim R As Range, V As Variant, i As Long
Set R = Range("A1:F47")
V = R.Value
Application.ScreenUpdating = False
For i = 1 To 300
    Set R = R.Offset(R.Rows.Count)
    R.Value = V
Next i
Application.ScreenUpdating = True
End Sub

 

[jim may]

** FORGET COPYING -- *** Work with the Page Setup set "PAGE-BREAKS" feature...

With your 14,100+ rows intact (layed-out contiguous) in your sheet

From a BACK-UP Copy of your file...

2 other housekeeping chores to do from your menu:
1) Cancel Print area
2) Find Page Layout (or its equiv depending on version) and from "Breaks" select "reset all page breaks".

Then Paste this code into a Standard Module:

Sub PrintAssignedNumbOfRowsPerPage()
    Dim iRow As Long
    Dim LastRow As Long
   
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        For iRow = 48 To LastRow Step 47 '48 is the row # you want to appear on Second (or #2 ) page
                                           'and 47 is the number of rows you want Per Page (to Print)
            .HPageBreaks.Add Before:=.Cells(iRow, "A")
        Next iRow
    End With


End Sub

 

[jim may]

Sorry, But I might have MISUNDERSTOOD your purpose. Looking Back I see you want to Copy the IDENTICAL Range of Rows 1-47 -- 300 times.
I took it that you have 14,000+ rows you wanted 47 rows per page... Again,,, Sorry Jim (+ I don't know how to DELETE a POST - I should LOL!!!

Another thought...

If you want to print 300 Pages - Why not Select Print Range Rows 1-47 and instruct the Printer to PRINT 300 Copies? Or am I missing
something here? Daaaaa..

 

[missouridawg]

Assuming you just want to copy the values in A1:F47 300 times:

Sub ThreeHundredTimes()
Dim R As Range, V As Variant, i As Long
Set R = Range("A1:F47")
V = R.Value
Application.ScreenUpdating = False
For i = 1 To 300
    Set R = R.Offset(R.Rows.Count)
    R.Value = V
Next i
Application.ScreenUpdating = True
End Sub

JoeMo - Thanks a ton for the code. This did exactly what I asked.

To further grow a little on what I'm working on... how would I improve this to include the formatting that comes with my first page? For example, Range (A5:A9) are merged with the text in cell A5 centered both vertically and horizontally... how would we adjust this code to accommodate that?

I also have borders around the majority of my boxes as well. How would I make the range (A1:B47) all have 4 borders per square, while range (C2:F33) only has an outside border for that gigantic square?

 

[missouridawg]

Sorry, But I might have MISUNDERSTOOD your purpose. Looking Back I see you want to Copy the IDENTICAL Range of Rows 1-47 -- 300 times.
I took it that you have 14,000+ rows you wanted 47 rows per page... Again,,, Sorry Jim (+ I don't know how to DELETE a POST - I should LOL!!!

Another thought...

If you want to print 300 Pages - Why not Select Print Range Rows 1-47 and instruct the Printer to PRINT 300 Copies? Or am I missing
something here? Daaaaa..

Jim - I appreciate you trying to help. Here's my full scenario.

I performed a survey. The excel data set that I have from the survey company is in sheet1. Every row of sheet 1 is an entry into the survey. Each row contains 90+ pieces of data. I want to take those 90+ pieces of data and put in into one formatted page. I need to perform this operation for every row of data.

What I'm trying to do, is to take each row of data (essentially one survey response) and make it into one printable page of information. Once I have that printable page of information, I can then sit down in front of a phone (as well as hand a portion of the stack of papers to a colleague) and we can follow up on the data from the survey to generate sales leads.

I feel confident that once I print all of these formats into their own individual pages on sheet2, I can then write a for loop that takes the data from sheet 1 and puts it into the right spot in sheet2. What I couldn't figure out how to do... is to take my blank formatted page and paste is 300 times for me using VBA. JoeMo did that, however, I need to get the right formatting included and then I'm good to go.

Thanks again for trying!

 

[pgc01]

Hi

To include the formatting try:

Range("A1:F47").AutoFill Destination:=Range("A1:F14147")

 

[JoeMo]

JoeMo - Thanks a ton for the code. This did exactly what I asked.

To further grow a little on what I'm working on... how would I improve this to include the formatting that comes with my first page? For example, Range (A5:A9) are merged with the text in cell A5 centered both vertically and horizontally... how would we adjust this code to accommodate that?

You are welcome - thanks for the reply.

In post #7 , pgc01 has provided the answer to your follow-on question. Put that line of code just before the Application.ScreenUpdating = True line.

 

[JoeMo]

You are welcome - thanks for the reply.

In post [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7]#7 [/URL] , pgc01 has provided the answer to your follow-on question. Put that line of code just before the Application.ScreenUpdating = True line.

Looking at this more closely I see that pgc01 has supplied a one-line solution for you - the one line in post #7 does what the OP requested, including the formatting.

 

[missouridawg]

Thank you folks so much. I appreciate yall taking the time out of your day to help me out.

You have saved me from hitting "ctrl V" around 350 times and I am forever grateful