Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,488
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

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

    Assuming you just want to copy the values in A1:F47 300 times:
    Code:
    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
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    7,449
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    ** 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:

    Code:
    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
    Last edited by jim may; Mar 8th, 2019 at 11:35 AM.
    To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie

  4. #4
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    7,449
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    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..
    Last edited by jim may; Mar 8th, 2019 at 11:41 AM.
    To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie

  5. #5
    New Member
    Join Date
    Mar 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by JoeMo View Post
    Assuming you just want to copy the values in A1:F47 300 times:
    Code:
    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?

  6. #6
    New Member
    Join Date
    Mar 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by jim may View Post
    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!

  7. #7
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,698
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

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

    Hi

    To include the formatting try:

    Code:
    Range("A1:F47").AutoFill Destination:=Range("A1:F14147")
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  8. #8
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,488
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

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

    Quote Originally Posted by missouridawg View Post
    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.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  9. #9
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,488
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

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

    Quote Originally Posted by JoeMo View Post
    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.
    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.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  10. #10
    New Member
    Join Date
    Mar 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •