+ Reply to Thread
Results 1 to 3 of 3

Error when running macro to copy and insert row

  1. #1
    Registered User
    Join Date
    07-15-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    7

    Error when running macro to copy and insert row

    Really stumped here. I just recently switched to Excel 2013 and some of my favorite macros are now generating errors. The odd part is that the macro will work perfectly when I first open my workbook, but if I make ANY changes to the table at all, then attempt to run the macro again, I receive Run-time error '-2147417848 (80010108)' Automation error The object involved has disconnected from its clients. The macro below was written to copy the topmost row of a table, insert the row immediately above (so it becomes the top row of the table), clear the contents of each cell (skipping over cells containing formulas), and return the active cell to the beginning of the row.

    Sub Add_Row()
    '
    ' Add_Row Macro
    '

    '
    Application.ScreenUpdating = False
    Range("A1").Select
    ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
    ActiveCell.Select
    Application.CutCopyMode = False
    Selection.ClearContents
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(0, -18).Range("A1").Select
    Application.ScreenUpdating = True
    End Sub

    The macro always seems to break on Selection.Insert Shift:=xlDown. Again, I can run the macro successfully when I first open the workbook, but if I add the copied/cleared row at the top and add information to it, then try to run the macro to add another copied/cleared row for my next entry, I get the error. Additional pain is that it crashes my excel and I have to close all workbooks (saved or not) and reopen before I can resume working. I've tried searching all over the web for a solution to this. HELP!

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,947

    Re: Error when running macro to copy and insert row

    Try making sure that the range is not converted to a listobject (table)

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    07-15-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    7

    Re: Error when running macro to copy and insert row

    Still getting hung up in the same spot. Is it the relative references that are causing the issue??

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. error when running MS Word macro commands in Excel macro
    By bsapaka in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-01-2014, 01:30 PM
  2. Saving copy of data and then running macro on all active worksheets in saved copy?
    By Science87 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-09-2014, 11:56 AM
  3. Insert a VBA code that run without running a macro
    By itselflearn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-16-2013, 06:10 PM
  4. [SOLVED] Macro to copy row, insert below, invert amt, and insert blank row between each new tx
    By raquel1227 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-07-2013, 12:56 PM
  5. [SOLVED] Error when running script to copy down formula
    By matty_d2002 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-06-2013, 07:55 AM
  6. Cannot find macro error when running a macro from a macro in a diffrent workbook.
    By Acrobatic82 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-05-2010, 09:22 AM
  7. Macro to insert copy and insert formulas only to next blank row
    By bob in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-30-2006, 07:10 AM

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