Copy and paste into blank cells only

[kris_91]

Hi all, I really hope you can help me. I have an issue writing my code. I have a range, where first I am inserting blank cells whenever the value/text in column N changes - this I have solved. Now I want to copy the header (row 1) into all the newly created blank rows. However this should only be done in a dynamic range (maybe using a formula to find last used row). Really hoping you can help.
Sub InsertRowsAtValueChangeColumn()
Dim X As Long, LastRow As Long
Const DataCol As String = "N"
Const StartRow = 2
LastRow = Cells(rows.Count, DataCol).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To StartRow + 1 Step -1
If Cells(X, DataCol).Value <> Cells(X - 1, DataCol) Then rows(X).insert
Next
'Application.ScreenUpdating = True
'rows(1).EntireRow.copy
'Selection.SpecialCells(xlCellTypeBlanks.PasteSpecial



'Range("A1:X1").Select
'Selection.copy
'BlankCell.Paste
'Destination = Selection.SpecialCells(xlCellTypeBlanks)


End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub InsertRowsAtValueChangeColumn()
Dim X As Long, LastRow As Long
Dim Hdr As Variant

Const DataCol As String = "N"
Const StartRow = 2
LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
Application.ScreenUpdating = False
Hdr = Range("A1:X1").Value2
For X = LastRow To StartRow + 1 Step -1
If Cells(X, DataCol).Value <> Cells(X - 1, DataCol) Then
   Rows(X).Insert
   Range("A" & X).Resize(, 24).Value = Hdr
End If
Next

End Sub

 

[kris_91]

wow it worked like a charm! Thank you so much. If I also want to copy the font of the header (row1) do you know how to do that??

 

[Fluff]

Do all the header cells have the same font? If so what is it?

 

[kris_91]

They all have the same colour
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0
.PatternTintAndShade = 0

 

[Fluff]

Is that the font colour or the interior colour?

 

[kris_91]

That is the interior colour! Font colour is just black

 

[Fluff]

Ok, how about

If Cells(X, DataCol).Value <> Cells(X - 1, DataCol) Then
   Rows(X).Insert
   With Range("A" & X).Resize(, 24)
      .Value = Hdr
      .Interior.ThemeColor = xlThemeColorAccent3
   End With
End If
Next