Paste a set of rows multiple times throughout a sheet

[philipg]

Hi all, I am looking to copy ten rows from the top of a sheet, and insert those ten rows hundreds of times throughout a sheet.

I have included a couple of images below - the ten rows to be copied are Cost1 to Cost10, and I want to insert these for each asset (Asset1 to Asset500 or so)

Thanks in advance!

 

[Zot]

Try this following your data structure posted.

Sub Asset()

Dim n As Long
Dim noAsset As String
Dim RngFound As Range, rngAsset As Range

Set RngData = Range("A4", "A13")

For n = 1 To 500
    noAsset = "Asset" & n
    Set rngAsset = Range("A15", Cells(Rows.Count, "A"))
    Set RngFound = rngAsset.Find(noAsset)
    If Not RngFound Is Nothing Then
        RngData.Copy
        RngFound.Offset(1).Insert Shift:=xlDown
    End If
Next

End Sub

 

[philipg]

Thanks Zot - As I wanted to copy entire rows I changed RngData to Rows("4:13"), and amended the Set rngAsset line to start at A14 so the costs were copied to the first and all subsequent assets. I'm totally new to VBA so if any of these changes have issues I'm not aware of please let me know! seems to be working well now though, thanks! Amended code below for 30 assets:

Sub Asset()

Dim n As Long
Dim noAsset As String
Dim RngFound As Range, rngAsset As Range

Set RngData = Rows("4:13")

For n = 1 To 30
    noAsset = "Asset" & n
    Set rngAsset = Range("A14", Cells(Rows.Count, "A"))
    Set RngFound = rngAsset.Find(noAsset)
    If Not RngFound Is Nothing Then
        RngData.Copy
        RngFound.Offset(1).Insert Shift:=xlDown
    End If
Next

End Sub