Inserting a Copied Row inside a table


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
 
Ad

Advertisements

I

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.
 
M

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?
 
I

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 :
 
I

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top