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 "Smiley" 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 "Smiley" 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 "Smiley" icon, below, in the bottom right corner :)

    Thanks a lot for your explanation ...


    Will draft a proposal for you ...

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

    Just noticed in one of your messages ... you do need not to erase previous copies ...


    Below is your Version 2 ...


    Let me know if it is in line with your expectations ...

    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 "Smiley" icon, below, in the bottom right corner :)

    Glad to hear it is all fixed


    Thanks for your Thanks ...AND for the Like ...:smile:

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

    Hi Carim,


    I’ve locked and protected cells A19:AL30 and range L4 but when I run the macro, it doesn’t work. I would like not to have anyone enter data in these fields.


    Please advise?


    Thanks,


    Z

    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 "Smiley" 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!

Register Yourself Login