Inserting a Copied Row inside a table

[magmike]

I have a table with a theme applied that makes the rows an alternating shade. I created a "template" row at the top of the table which carries all theformatting, conditional formatting and formulas I want replicated in each new row. When I select that template row as a whole and copy it, then select another row I would like to insert it above, right-click and select "insert" I get the following error:

"This operation is not allowed. The operation is attempting to shift cells in a table on your worksheet."

I find however, when I simply select A2:AK2 (instead of the whole row) thenselect the row I want to insert it above, right-click and select "insert copied cells" it will allow this.

If there is not a way to fix the sheet where I can insert an entire copied row like mentioned in the first way, does anyone know how to do the following:

Create a button that when pushed would select and copy A2:AK2 and then insert those copied cells above the row that the currently selected cell is in?

Does that all makes sense?

Thanks in advance for your help,
magmike

 

[isabelle]

hi magmike,

Range("A2:AK2").Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

isabelle

Le 2013-09-04 19:54, magmike a écrit :

I have a table with a theme applied that makes the rows an alternating shade.

I created a "template" row at the top of the table which carries all
the formatting, conditional formatting and formulas

I want replicated in each new row. When I select that template row as a
whole and copy it,

then select another row I would like to insert it above, right-click and
select "insert" I get the following error:

"This operation is not allowed. The operation is attempting to shift cells in a table on your worksheet."

I find however, when I simply select A2:AK2 (instead of the whole row) then select the row

I want to insert it above, right-click and select "insert copied cells"
it will allow this.

 

[magmike]

Beautiful!

Would it be too hard to make a box come up and warn the user with an OK to continue or CANCEL to abort?

 

[isabelle]

ok magmike but it would be best to check first if the selection is in
column A ,

Sub test()
Dim Msg, Style, Title, Response
Msg = "do you wish to continue "
Style = vbYesNo + vbInformation
Title = "Copy template"

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
If Selection.Column = 1 Then
Range("A2:AK2").Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Else
MsgBox "You must first select a cell in column A"
End If
Else
'anything else
End If
End Sub

isabelle

Le 2013-09-04 20:35, magmike a écrit :

 

[isabelle]

correction for the second msgbox,

Sub test()
Dim Msg, Style, Title, Response
Msg = "do you wish to continue "
Style = vbYesNo + vbInformation
Title = "Copy template" ' Définit le titre.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' L'utilisateur a choisi Oui.
If Selection.Column = 1 Then
Range("A2:AK2").Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Else
MsgBox "You must first select a cell in column A", vbInformation, Title
End If
Else
'anything else
End If
End Sub

isabelle

 

[magmike]

Thank you!

 

[isabelle]

it is my pleasure

isabelle

Le 2013-09-04 21:13, magmike a écrit :