I have an operation that works manually and a Macro that does the same thing that works in a Region
but NOT in a Table.
I am using Excel from MS Office 2016 Home & Student on Windows 10.
In my test there is data in columns A-G and I (nothing in H).
I select the cells in, say, A4-G4 and insert cells (not a row).
I select A4-G5 and fill down.
Then I do some manipulation on the new row 4.
Column I remains in tact (as desired).
I created the macro below based on a recording (which won't run due to an
application error that I didn't write down) and then some web searches.
If the data is in a Table (like my real data with a few hundred rows) it fails with
Error 1004: this won't work because it would move cells in a table on your worksheet
at row 03 in the macro below.
There are no other Tables in the Workbook.
If I demote the Table to a 'mere' Region, it works fine!
Please help. I want to use the macro in a Table.
01 Sub Macro2
02 With Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 7)) ' select data row
03 .Insert xlShiftDown ' push other data down and insert a blank row
04 End With
05
06 Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(0, 7)).Select ' select the row above and the new row
07 Selection.FillDown ' copy the row above into the new row
08
09 ' update the new row...
10 Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 1)).Select
11 Selection.Copy
12
13 Range(ActiveCell.Offset(1, 1), ActiveCell.Offset(1, 1)).Select
14 ActiveSheet.Paste
15
16 Application.CutCopyMode = False
17 ActiveCell.Offset(0, -1).Select
18 End Sub
Capture.PNG
EDIT: When I do the operation manually (or when trying to record a Macro) I select Insert > Cells > Shift Down from the ribbon. I have attached the test file (including Macro2).
Bookmarks