Copy/paste Row 1 after every merged cell

[omkar94]

Hey Guys!

Im looking for a VBA code that will copy row 1 from column A to M , and will paste row 1, a line below every time it finds a merged cell. And I want this to loop until the last Merged cell. Please refer to my code below and see if it makes any sense to you.

Can someone please help. Im sure this is a very simple code, but Im just a beginner and cant figure it out.

My Code:

Sub Insertrow1 ()

Rows("1:1").Select
Selection.Copy
' Find next merged cell (Need help on this line)
ActiveCell.Offset(1, 0).Select
Selection.Insert Shift:=xlDown

' Loop (Need Help on this line)

End Sub


Thank you,

Omkar V

 

[igold]

Merging cells is never a good idea, especially when you are using VBA on a sheet that contains merged cells. That said, see if this does what you want...

Sub findMerged()
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim c As Range, r1 As Range, rng As Range
    Dim sMsg As String
    Dim i As Long, rw As Long
    
    Set r1 = Rows("1:1")
    Set rng = ws.UsedRange
    rw = rng.Rows.Count
    For i = rw To 2 Step -1
        For Each c In rng.Rows(i).Cells
            If c.MergeCells Then
                r1.Copy
                ws.Range("a" & i).EntireRow.Offset(1, 0).Insert
                Exit For
            End If
        Next c
    Next
    Application.CutCopyMode = False
    
End Sub

I hope this helps.

 

[omkar94]

Oh man, you're like VBA god me right now!
Thanks a bunch, it works!!

Btw, the merged cells are there because, It is a report I export daily from a website, and I want to create a Pivot table after every merged cell. So I am going to try to create a code for that as well.
I'm also trying to learn VBA, any tips on how to be proficient at it?

Regards,

Omkar V

 

[igold]

Thanks for the feedback. Like Vocabulary, you just have to want to learn it and keep practicing. I did not get your code right the first time, but I kept trying. I am a noob compared to a lot of people on this forum.