Inserting a Copied Row inside a table

  • Thread starter Thread starter magmike
  • Start date Start date
M

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
 
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.
Click to expand...

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
Click to expand...

I want to insert it above, right-click and select "insert copied cells"
it will allow this.
 
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?
 
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 :
 
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
 
Back
Top