Macro That Works in a Region but Fails in a Table

[TomInCA]

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

[TMS]

This:

Please Login or Register  to view this content.

Needs to be this:

Please Login or Register  to view this content.

[TomInCA]

Thank you! My brain didn't calculate the offset correctly. I stepped over the Table boundary and didn't see that because the debugger on top of it. You clearly saw the solution but I don't know how to mark it so..

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.