Copy Paste on last column X number of times

  • Hi Everyone,


    I'd appreciate it if anyone can help me automate a process. I've attached a workbook where I would like to enter a number in column H4 that will copy x number of times column A19:AL30 after the last column on the same work sheet. Let say I would type 15 in column H4, therefore I would like it to copy columns A19:AL30 15 times after the last line.



    Thanks so much,


    Z

  • Hi,


    Just to clarify your objective ...


    Are you looking for extending your section : Template # ???


    Which would require two things :


    1. Insert rows in order to keep your Total formulas


    and


    2. Copying the formula in cell AL21 ... to newly inserted rows


    Thanks for your comments

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi again,


    If my understanding is correct, you could test following


    Code
    Sub InsertRows()
    Dim last As Long, x As Long
        last = Cells(Rows.Count, 1).End(xlUp).Row
        x = Range("H4").Value
        Rows(last + 1 & ":" & last + x).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("AL" & last).Copy Destination:=Range("AL" & last + 1 & ":" & "AL" & last + x)
    End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,


    Thank you for responding. I'm not looking to extend but to copy the whole section down from field A19:AL30 (Template#).
    I had used below code which sorta works but it overrides my data when I enter number again in column H4 if I need to add more templates.
    I would like it to paste in the last row and that it will not override into the previously added templates where it was inputted from H4


    Dim rng As Range

    Set rng = Range("A19:AL30")

    rng.Copy rng.Resize(Range("H4") * rng.Rows.Count)


    End Sub



    Thank you,


    Z

  • Sorry ... but I do not understand what you want ...


    Could you take advantage of your test file ... to include the final result you are expecting ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • This is great, thank you so much!


    One more thing, is it possible to lock cells: A:19:AL30 and still run the macro. I prefer not to have data entered in the "1st template" where it's being pasted down.


    Also, I noticed that when I enter 5 in cell H4 and replicate, the templates are not one after another as there are empty cells in between. But if I enter 1 each time and replicate, it's in alignment with previous copied template.


    Thank you for your help!

  • Problem should be solved with the Calculation instruction


    Code
    Sub CopyTemplateSection()
    Dim i As Long
    Application.Calculation = xlCalculationManual
    For i = 1 To Range("H4").Value
      Range("A19:AL30").Copy Destination:=Range("A" & Range("L4") + (i * 12))
    Next i
    Application.Calculation = xlCalculationAutomatic
    End Sub

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Well ... if you are working with a protected sheet ...


    At the top of your macro .. you need to Unprotect ...


    And at the end of your macro .. you need to Protect again ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!