Creating a macro that will copy down content from certain cells
Results 1 to 6 of 6

Thread: Creating a macro that will copy down content from certain cells
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Creating a macro that will copy down content from certain cells

    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!

  2. #2
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,277
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Creating a macro that will copy down content from certain cells

    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 by JackDanIce; Aug 14th, 2019 at 10:41 AM.


  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating a macro that will copy down content from certain cells

    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?

  4. #4
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,277
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Creating a macro that will copy down content from certain cells

    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 by JackDanIce; Aug 15th, 2019 at 05:26 AM.


  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating a macro that will copy down content from certain cells

    Thank you, Jack! That worked! Very much appreciated.

  6. #6
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,277
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Creating a macro that will copy down content from certain cells

    You're welcome


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •