Macro Help - Copy and Insert Multiple rows repeating

[markpassmore]

I want to copy 8 rows, and then insert these on the next row down after the 'product' cell row, as a repeating macro, but haven't figured it out.

My image pasted shows the rows in yellow, where i have manually copied these, and then insert copied cells.

i really need help in how to automate a macro to copy and insert all the way through the spreadsheet as there are over 3000 product lines to insert these rows after each time (its for a shopping cart drop down variant choice that imports csv files)

Any help please, its probably simple but im a novice with macros.

Thanks, mark

 

[mackc557]

This is my attempt at your problem. Select the last group of cells you would like to copy, and as long as there are non-blank cells directly below the selection, it will keep going until it completes the last one or gets to the safety stop number (Counter which can be changed if 1000 is too small or too large of a number).

Hope it helps.

Sub Replicate()
Dim AddressA As Range
Dim AddressB As Range
Dim ToCopy As Range
Dim i As Integer
Dim Counter As Integer

'This is a safety precaution as well as you can change this number
'to set a certain number of items to run through
Counter = 1000



Set ToCopy = Application.Selection
Set AddressA = ToCopy.Cells(ToCopy.Rows.Count + 2, 1)
Set AddressB = AddressA.Offset(1)

i = 0

While AddressA.Cells <> "" Or i > Counter
ToCopy.Copy
AddressA.Insert xlShiftDown
Set AddressA = AddressB
Set AddressB = AddressA.Offset(1)
i = i + 1
Wend

End Sub

 

[markpassmore]

fantastic, did it exactly as i wanted. thank you very much mackc557
just awesome

 

[mackc557]

fantastic, did it exactly as i wanted. thank you very much mackc557
just awesome

Glad I could help.

 

[Peter_SSs]

@mackc557
When posting vba code, please use one of the relevant code tags, not quote tags. My signature block below has more details.

 

[mackc557]

@mackc557
When posting vba code, please use one of the relevant code tags, not quote tags. My signature block below has more details.

Thank you for the correction, I will keep this in mind in the future. I would edit it, but it seems I'm not able to now.

 

[markpassmore]

hoping you can help again, as i still not grasping how to program a macro
this spreadsheet has a list of catalogue products, each product having one row, followed by four rows of 'variant' details.
What i would like to do is keep all the 'product' rows, but have all the variant lines be deleted. database consists of about 2500 product rows
if you are able to help, again would be appreciated

Thanks, Mark

 

[Peter_SSs]

I would edit it, but it seems I'm not able to now.

No, you only have 10 minutes after posting to edit. Adding code tags wouldn't cause such a problem but the restriction is in place because threads can become completely meaningless if a post early in a thread is changed at a much later time.