Need macro to copy a hidden row

[Billy Thomas]

I have a spreadsheet with rows 701 identical rows [rows 6 - 706]. These rows
contain formulas, data validation, merged cells, and conditional formats. As
I enter data into the rows, I sometimes need to go back and insert a row
with the same formulas, validation, merged cells, etc. I thought the best
way to accomplish this would be to hide row 6 (as I sometimes will change
one of the rows by adding an @sum or something, so I can't be sure that the
row above where I currently am working is unaltered, so I figured to use a
hidden row as a "template") then access it [row 6] to copy and insert where
I need it. Can someone please help me with a macro that will copy and insert
this hidden row from any of the other 700 rows. I am using Excel 2000 for
Windows.
thanks,
Billy

 

[Dave Peterson]

I'd put a button from the Forms toolbar in Row 1. Then use Windows|FreezePanes
to make sure that row 1 is always visible.

Then assign this macro to the button:

Option Explicit
Sub testme01()

Dim myMstrRow As Long
Dim curRow As Long

myMstrRow = 6

curRow = ActiveCell.Row
Rows(curRow + 1).Insert
Rows(myMstrRow).Copy _
Destination:=Cells(curRow + 1, "A")
Rows(curRow + 1).AutoFit

End Sub

I have a spreadsheet with rows 701 identical rows [rows 6 - 706]. These rows
contain formulas, data validation, merged cells, and conditional formats. As
I enter data into the rows, I sometimes need to go back and insert a row
with the same formulas, validation, merged cells, etc. I thought the best
way to accomplish this would be to hide row 6 (as I sometimes will change
one of the rows by adding an @sum or something, so I can't be sure that the
row above where I currently am working is unaltered, so I figured to use a
hidden row as a "template") then access it [row 6] to copy and insert where
I need it. Can someone please help me with a macro that will copy and insert
this hidden row from any of the other 700 rows. I am using Excel 2000 for
Windows.
thanks,
Billy

 

[Billy Thomas]

thank you so much for the macro. one question.......can row 6 be hidden?

 

[Dave Peterson]

I hid it in my testing and it seemed to work ok.

Didn't it work ok for you?

thank you so much for the macro. one question.......can row 6 be hidden?

I have a spreadsheet with rows 701 identical rows [rows 6 - 706]. These rows
contain formulas, data validation, merged cells, and conditional formats. As
I enter data into the rows, I sometimes need to go back and insert a row
with the same formulas, validation, merged cells, etc. I thought the best
way to accomplish this would be to hide row 6 (as I sometimes will change
one of the rows by adding an @sum or something, so I can't be sure that the
row above where I currently am working is unaltered, so I figured to use a
hidden row as a "template") then access it [row 6] to copy and insert where
I need it. Can someone please help me with a macro that will copy and insert
this hidden row from any of the other 700 rows. I am using Excel 2000 for
Windows.
thanks,
Billy