Solved Excel VBA - Copy range of cells and paste to next row x amount of times (1 Viewer)

[ChrisMore]

Hi,

Can someone please help me write some code which copies C2 : D6 (including formulas) and pastes this to the next available row underneath. This will need to loop 4932 times. I also need the code to fill down columns A, B, F, G, H, I and J until the last row. Column A will need to be filled down so it continues the number sequence.

I can see lots of forum posts similar to mine when Googling but haven't been able to tweak any of the code to work successfully for what I require.

I have attached a screenshot of my sheet which might help.

Thanks very much,
Chris

 

[Isaac]

- Check out the range.copy method to do the copy
- Have a rngPaste variable declared (as range)
- Each time the copy happens, set rngPaste = rngPaste.Offset(4,0)
- use rngPaste.Paste

That might get you started

 

[Gasman]

- Check out the range.copy method to do the copy
- Have a rngPaste variable declared (as range)
- Each time the copy happens, set rngPaste = rngPaste.Offset(4,0)
- use rngPaste.Paste

That might get you started

@Isaac ,
Shouldn't the offset be (5,0) ?

 

[Isaac]

@Isaac ,
Shouldn't the offset be (5,0) ?

I knew that - I was just testing everyone's mental math abilities, as mine are stellar......................

 

[ChrisMore]

- Check out the range.copy method to do the copy
- Have a rngPaste variable declared (as range)
- Each time the copy happens, set rngPaste = rngPaste.Offset(4,0)
- use rngPaste.Paste

That might get you started

Hi Isaac,

Unfortunately I require some more help with this. I am new to VBA but trying to learn it so I appreciate you not providing me with the exact code straight away.

I have followed your advice pretty much line for line and I have got an error. The error is with the line including the offset. It's my lack of understanding of how to structure the code which also trips me up.

This is what I've done so far:

Sub Create_All_Data()

Dim rngPaste As Range

Range("B2:D6").Copy
rngPaste = rngPaste.Offset(5, 0)
rngPaste.Paste

End Sub

 

[Gasman]

From this link https://www.mrexcel.com/board/threa...e-multiple-times-based-on-cell-value.1056440/
this will do what you want for B2 : D6

Range("B2:D6").Copy Range("B7").Resize(7 * 4932)

Pretty neat for a one liner. Hopefully you can use this logic for the rest of your worksheet?
I've tested it for 5 iterations, you might want to do the same?

HTH

 

[ChrisMore]

From this link https://www.mrexcel.com/board/threa...e-multiple-times-based-on-cell-value.1056440/
this will do what you want for B2 : D6

Range("B2:D6").Copy Range("B7").Resize(7 * 4932)

Pretty neat for a one liner. Hopefully you can use this logic for the rest of your worksheet?
I've tested it for 5 iterations, you might want to do the same?

HTH

Wow, thanks Gasman, that is powerful little piece of code. I don't really understand what the resize bit is doing. What does the 7 represent?

I am getting an overflow error for 7*4932 but it works amazingly for a smaller number. Did you test it for 7*4932 and get an overflow error too?

 

[Gasman]

No, I did not test to that number.
I have just done so and I also get overflow.

I suggest you go to that link and ask Fluff, or work around it with some simple math.?

In actual fact, 7 is incorrect and should be 5 as that is the number of rows you are copying.
The benefit now is that 5 * 4932 works.

 

[Minty]

It will only work up to a calculated value of 32768, as that's the maximum integer value in VBA

 

[Gasman]

However, you can get around that like this.
Also tested.

Dim lngRow As Long, lngTimes As Long
lngRow = 7
lngTimes = 4932
Range("B2:D8").Copy Range("B9").Resize(lngRow * lngTimes)

 

[Isaac]

Absolutely brilliant, @Gasman
This is also a real tribute to the intelligent way Excel copies & pastes formulas and the whole relative reference thing. Here I was focused on the 'loop' as suggested by OP and didn't even stop to realize a single copy may do it.

Newly enlightened as I now am, I suspect you could even skip the math and simply code something like:

Range("C2:D6").Copy Range("C7:D40000")

(I just made up that last number - I'll let you work out the actual row you want this going down 'to')

Note - this suggestion will depend entirely on how you've carefully setup the original formulas to be relative in the right spots, and/or absolute in the right spots.

 

[Gasman]

No credit to me.
I just googled and came upon that link by Fluff, and amended it (incorrectly at first) to suit.

I too was trying with a loop, but could not get it correct, so decided to Google instead.

In the link, the values were coming from Excel cells?

 

[Isaac]

Fluff is a cool nickname

 

[ChrisMore]

I have the code all working perfectly now, so thanks Gasman and Isaac (and Fluff) for your assistance