VBA to copy and paste a range multiple times based on cell value
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 31

Thread: VBA to copy and paste a range multiple times based on cell value

  1. #11
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,976
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA to copy and paste a range multiple times based on cell value

    Because it's copying 14 rows.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  2. #12
    Board Regular
    Join Date
    Oct 2018
    Posts
    209
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy and paste a range multiple times based on cell value

    Hi, would it be possible to add to your code below to copy the row height from the original range to the new range? Thanks

    Code:
    Sub CopyMulti()
    Range("A1:D14").Copy Range("A15").Resize(14 * Range("E1").Value)
    End Sub

  3. #13
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,976
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA to copy and paste a range multiple times based on cell value

    Are the row heights all the same?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #14
    Board Regular
    Join Date
    Oct 2018
    Posts
    209
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy and paste a range multiple times based on cell value

    Yes they are

  5. #15
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,976
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA to copy and paste a range multiple times based on cell value

    In that case use
    Code:
    Sub CopyMulti()
    Range("A1:D14").Copy Range("A15").Resize(14 * Range("E1").Value)
    Range("A15").Resize(14 * Range("E1").Value).RowHeight = Range("A1").RowHeight
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #16
    Board Regular
    Join Date
    Oct 2018
    Posts
    209
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy and paste a range multiple times based on cell value

    Thank you works great!

  7. #17
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,976
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA to copy and paste a range multiple times based on cell value

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: VBA to copy and paste a range multiple times based on cell value

    Hey, good morning. This thread has been massively useful for me so far - thanks! However I have the situation where the row heights in the original range are not all the same but need to be retained. Would you please be able to advise on how to achieve this?

  9. #19
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,976
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA to copy and paste a range multiple times based on cell value

    Hi & welcome to MrExcel.
    You will need to loop through the rows, setting the rowheight one by one.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: VBA to copy and paste a range multiple times based on cell value

    Hi Fluff,

    I'm very new to VBA and this thread was also super helpful for me. I used your single line of code to copy a chunk of data and paste it below the original chunk repeatedly based on a number in a cell. However, when I try to use that code to paste the chunk of data to the left of the original chunk repeatedly based on the number in the cell, it only pastes it once instead of the number of times indicated. Is there a way to resolve this? Why does it only work pasting downward instead of to the left? Thanks in advance!

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
  •