Insert Rows And Copy Formula Cells

I am trying to finish off a form that allows a user to insert a row below the selected cell and copy the formula from the line above if one exists. The code inserts the line but does not copy the formulas if they exist. any help is appreciated.

Guy

Application.ScreenUpdating = False


    Dim cRow
    Dim j As Long
    
    cRow = ActiveCell.Row
    
            With ActiveCell
                .EntireRow.Insert
            End With
                 
            For j = 1 To Cells(1, 255).End(xlToLeft).Column
                If Cells(cRow, j).HasFormula Then Cells(cRow, j).Copy Cells(cRow + 1, j)
            Next j
             
            Application.ScreenUpdating = True
            Application.CutCopyMode = False
            
    Unload Add_Date_Form
    
End Sub


Private Sub CommandButton2_Click()
     Unload Add_Date_Form
End Sub






Private Sub UserForm_Initialize()


    Dim cRow
    
        cRow = ActiveCell.Row ' remember the current row
        TextBox1 = Cells(cRow, Range("column_date").Column).Value


End Sub

Re: Insert Row And Copy From Line Above If There Is A Formula

Ger,
Thanks for the advice. That helped a lot. I was testing the new row for formulas but it was a new row and empty. The fixed code is below for reference.

Now, there still is a problem of it not finding formulas for most of my sheet - the part where the columns are hidden. I have 1/2 my sheet hidden. The macro does not copying and paste them as I think it should. Could this be because they are hidden? Could it be because I have from column AP to the end column hidden? I have Column J & L also hidden but the macro DOES copy those formulas. Why would it not copy those formulas of the hidden set that goes from AP to the end?

On a hunch I tried just hiding 5 of the columns that didn't copy and ran the code again. It copied fine. Next I unhid the last column and just hide AP over two the second to last column. The code did NOT copy the info. So, it looks as though the code works when some of the columns are hidden but after a certain amount of hidden columns then it no longer works. What am I doing wrong?

Guy

Re: Insert Row And Copy From Line Above If There Is A Formula

Quote from Dave Hawley

Use the SpecialCells Method and restrict to ONLY formulas.

.End(Xldirection) will NOT stop at hidden rows or columns.

Dave, thanks for your reply. Interesting idea. I have never tried that way. This is the code I developed from your idea...

For j = 1 To Cells(1, 255).SpecialCells(xlCellTypeFormulas)
                If Cells(cRow, j).HasFormula Then Cells(cRow, j).Copy Cells(cRow + 1, j)
            Next j

It does the same thing because I think I am doing it wrong. So I tried this way but it still does the same thing.

For j = 1 To Cells(1, 255).SpecialCells(xlCellTypeFormulas)
                Copy Cells(cRow + 1, j)
            Next j

So, what am I doing wrong? How do I get excel to copy rows even when hidden?

Thanks much in advance.

Guy

Re: Insert Rows And Copy Formula Cells

Quote from Ger Plante

Dave - OMG!!!! I cant believe you used a loop!!! ::D

Here is a loopless alternative. Not sure if it would be any more efficient. And it would probably copy formatting too which you dont want.

Code

Sub MyMacro2()


    
    Rows("2:3").FillDown 'copy contents of entire row
    Rows(3).SpecialCells(xlCellTypeConstants).ClearContents 'remove anything that isnt a forumla
        
End Sub

Ger

Display More

Ger, nice catch. I understand your code will work with set rows like "2:3" but will it work with unsing variable set rows like I have (ie cRow and cRow+1)? The user picks a row location where they want to add a row and fill that new row with all the formulas from the row being copied (but, as you note, none of the data).

Man, this little macro is giving me all sorts of fits.

Thanks in advance

Guy