Solved Excel Macro to copy and insert row

Microsoft Office excel 2007 - upgrade
December 21, 2010 at 11:44:51
Specs: Windows XP
Hi,

Please help with the following...

Using a command button, I would like to have a macro copy the entire row of a selected cell, including formulas, insert the new row directly above the selected cell and clear the contents(not the formula). I also need a message box to prompt the user if they wish to proceed with adding the new row....msg "Add new Row?", if yes then run macro, if no then exit.

New to VB, Any help is Greatly Appreciated!


See More: Excel Macro to copy and insert row

Report •

✔ Best Answer
December 22, 2010 at 08:26:24
Thanks for your help Derby.
This is what I ended up with.

Sub CommandButton1_Click()
Dim Msg, Style, Title, Response
Msg = "Add New Project?"   
Style = vbYesNo + vbQuestion + vbDefaultButton1    
Title = "Add New Project"    
Response = MsgBox(Msg, Style, Title)
On Error GoTo noconstants
If Response = vbYes Then      
       With ActiveCell.EntireRow
      .Copy
      .Insert Shift:=xlDown
      .SpecialCells(xlCellTypeConstants).ClearContents
    End With
Else    
    Exit Sub
End If
noconstants:
    Application.CutCopyMode = False
    Exit Sub

End Sub



#1
December 21, 2010 at 12:32:01
re: "clear the contents(not the formula). "

I'm not sure what you mean by that.

In VBA, clearing the contents means clearing the contents. If there is a formula in the cell, that's the "contents".

What else besides that would there be to clear?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 21, 2010 at 12:46:16
Thanks for the quick reply.
I hope this clarifies

I want it to clear cells with constants in them, not cells with formulas.

This is for a time sheet, tracking hrs worked on specific projects, when a new project(row) is added, I need it to clear the hours for each day but not the formulas keeping total hrs.

Thanks Again


Report •

#3
December 21, 2010 at 12:54:52
From excel help, it seems the "Range.SpecialCells Method" can do this. I don't know how to set it up to achieve this though

Report •

Related Solutions

#4
December 21, 2010 at 14:43:28
I added a little error checking since the SpecialCells method will fail if there are no constants in the copied row.

As always, test this code in a backup copy of your workbook since macros cannot be undone.

Sub AddRowDeleteConstants()
 On Error GoTo NoConstants
   With ActiveCell.EntireRow
      .Copy
      .Insert Shift:=xlDown
      .SpecialCells(xlCellTypeConstants).ClearContents
    End With
  Exit Sub
NoConstants:
 MsgBox "There were no constants to delete"
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
December 22, 2010 at 08:26:24
✔ Best Answer
Thanks for your help Derby.
This is what I ended up with.

Sub CommandButton1_Click()
Dim Msg, Style, Title, Response
Msg = "Add New Project?"   
Style = vbYesNo + vbQuestion + vbDefaultButton1    
Title = "Add New Project"    
Response = MsgBox(Msg, Style, Title)
On Error GoTo noconstants
If Response = vbYes Then      
       With ActiveCell.EntireRow
      .Copy
      .Insert Shift:=xlDown
      .SpecialCells(xlCellTypeConstants).ClearContents
    End With
Else    
    Exit Sub
End If
noconstants:
    Application.CutCopyMode = False
    Exit Sub

End Sub


Report •

#6
December 22, 2010 at 09:49:51
Glad I could help out.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
April 10, 2011 at 11:43:41
Can you adjust that macro to insert the row ABOVE and not BELOW? I can't quite figure out how to do it. Otherwise it works perfect for me. Thanks in advance!

Report •

Ask Question