Results 1 to 4 of 4

Macro That Works in a Region but Fails in a Table

Threaded View

  1. #1
    Registered User
    Join Date
    05-05-2021
    Location
    Orange County, CA
    MS-Off Ver
    2016 Home & Student
    Posts
    8

    Macro That Works in a Region but Fails in a Table

    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).
    Last edited by TomInCA; 05-05-2021 at 07:33 PM. Reason: Additional Info (Test Data)

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Japanese VBE/Function Name, Locale Region not works
    By doriangrey in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-05-2020, 02:42 PM
  2. [SOLVED] Macro fails to Record String Results, Step Through Works
    By truk2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-21-2019, 05:12 AM
  3. [SOLVED] macro works but when added to button macro, it fails, dont know why
    By dmcgov in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-06-2016, 01:20 PM
  4. Replies: 2
    Last Post: 06-01-2015, 06:23 PM
  5. Code fails when ran within Userform, but works when ran from Macro button on worksheet.
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 6
    Last Post: 11-29-2013, 02:28 AM
  6. Macro run fails, step through works.
    By SteveAlston in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-15-2009, 11:37 PM
  7. Macro works fine on PC; fails on Mac
    By marlea in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-20-2006, 04:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1