Creating a macro that will copy down content from certain cells

UTdawg

New Member
Joined
Jul 30, 2019
Messages
6
Hi all,

I've created a macro in my excel sheet that will copy down all of the formulas when I insert a row. I'm using the following code to do this:

Sub InsertRowFormulas ()
Selection.EntireRow.Insert
For Each cell In Intersect (ActiveSheet.UsedRange, Selection.Offset (-1, 0). EntireRow)
If cell.Has Formula Then
cell.Copy cell.Offset (1,0)
End If
Next
End Sub

However, there are some columns with an "H" in them which denotes a holiday. I would like for my macro to copy down these H's as well when inserting a new row. Any advice or help on how to do this would be greatly appreciated.

Thanks!
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,674
Office Version
365
Platform
Windows
Try:
Code:
Sub M1()

    Dim x   As Long
    Dim c   As Long
        
    Application.ScreenUpdating = False
    
    With Selection
        .EntireRow.Insert
        .SpecialCells(xlCellTypeFormulas).Copy
        .Offset(1).PasteSpecial xlPasteFormulas
        Application.CutCopyMode = False
        x = 1
        Do
            If .Cells(1, x).Value = "H" Then .Cells(2, x).Value = "H"
            x = x + 1
        Loop Until Len(.Cells(1, x).Value) = 0
    End With
                
    Application.ScreenUpdating = True
    
End Sub
 
Last edited:

UTdawg

New Member
Joined
Jul 30, 2019
Messages
6
Unfortunately that didn't work. I got an error message saying Run-time error '1004'

Application-defined or object-defined error

Any advice on what to do now?
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,674
Office Version
365
Platform
Windows
Tested and this works for me, try:
Code:
Sub M1()

    Dim r   As Range
    
    Application.ScreenUpdating = False
        
    Selection.EntireRow.Insert
    
    For Each r In Intersect(ActiveSheet.UsedRange, Selection.Offset(-1).EntireRow)
        With r
            If .HasFormula Then .Copy .Offset(1)
            If .Value = "H" Then .Offset(1).Value = "H"
        End With
    Next r
    
     With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
            
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,369
Messages
5,486,449
Members
407,547
Latest member
Sankarasrinivas

This Week's Hot Topics

Top