Selection.Insert Shift:=xlDown NOT WORKING!?!?!?!?

[VBA FTW]

 Selection.Insert Shift:=xlDown

This piece of code is driving me nuts! All I need to do is insert my copied rows onto a new spreadsheet, but it keeps getting hung up on the above code. Here's what I don't understand: I'm using this EXACT code in another workbook and it works flawlessly. My code in full is as follows:

Sub ContractorsTab()
'
' ContractorsTab Macro
' Adds selected rows to 2012 Sent Invoice Summary Sheet
'

'
    Selection.Copy
    Workbooks.Open Filename:="X:\OSC DETAIL\Invoices Sent to AP\Sent Invoice Summary - 2012.xlsx"
    Windows("Sent Invoice Summary - 2012.xlsx").Activate
    Sheets("Contractors").Select
    Rows("4:4").Select
    Selection.Insert Shift:=xlDown
    ActiveWorkbook.Close SaveChanges = False
    Windows("OS Invoices 07-15.xlsm").Activate
End Sub

All I'm trying to do is put this code into another workbook. My new workbook name is OS Invoices.xlsm, which, in the last line above, I have changed on the new workbook. Anyone have any ideas on why

Selection.Insert Shift:=xlDown

isn't working on the new book? I'm about to go postal here It just doesn't make sense, it works in one workbook but not the other? WHY!?

[JosephP]

what exactly is the problem? error? slow processing? other?

[VBA FTW]

Run-time error '1004':
Copy method of Range class failed

Then I hit debug and Selection.Insert Shift:=xlDown is highlighted

[JosephP]

try

Sub ContractorsTab()
'
' ContractorsTab Macro
' Adds selected rows to 2012 Sent Invoice Summary Sheet
'

'
dim rCopy as range
dim wb as workbook
    set rcopy = Selection
    set wb = Workbooks.Open (Filename:="X:\OSC DETAIL\Invoices Sent to AP\Sent Invoice Summary - 2012.xlsx")
    wb.Sheets("Contractors").Rows("4:4").Insert Shift:=xlDown
    wb.Close SaveChanges = False
    Windows("OS Invoices 07-15.xlsm").Activate
End Sub

but I don't understand why you don't want to save the workbook-seems to make it pointless code

[VBA FTW]

I do want to save the workbook. SaveChanges = False saves it, SaveChanges = True doesn't save it. I don't really understand the logic behind it though. I'll give your code a try once Excel stops freezing on me

---------- Post added at 03:31 PM ---------- Previous post was at 03:18 PM ----------

Your code is close, but it isn't pasting my copied selection. Even though I'm selecting 12 rows, its only inserting 1 row, but it isn't putting anything in the cells, it's just a blank row

[shg]

I do want to save the workbook. SaveChanges = False saves it, SaveChanges = True

Only because of the way you've written it. It should be

ActiveWorkbook.Close SaveChanges:=True

That's how you assign a value to a named argument.

As is, it is evaluating {undeclared variant coerced to Boolean} = False, which evaluates to True, which is passed positionally.

If you used Option Explicit at the top of the module, you'd never have that problem.

[VBA FTW]

Only because of the way you've written it. It should be

ActiveWorkbook.Close SaveChanges:=True

That's how you assign a value to a named argument.

As is, it, it is evaluating {undeclared variant} = False, which evaluates to True,

If you used Option Explicit at the top of the module, you'd never have that problem.

AAhhhh I see. Thanks for the explanation!

---------- Post added at 04:01 PM ---------- Previous post was at 03:54 PM ----------

I think I may have found where the problem lies, but not sure how to fix it yet. On my original workbook, like I said, if I select the rows I need to copy and hit my macro button, it works fine. It copies my selected rows, opens my other workbook, selects the appropriate sheet, selects row 4, inserts my selected rows downward starting at row 4, saves, closes, activates my book that has the macro button, and clears the contents of the rows that were initially selected. But when I enter new data into the rows, select them, and hit my macro button, for some reason it doesn't seem to be copying the rows I've selected. So the problem seems to be there's nothing on the clipboard I'm guessing. Any ideas on how to get passed this based on the below code?

Sub ContractorsTab()
'
' ContractorsTab Macro
' Adds selected rows to 2012 Sent Invoice Summary Sheet
'

'
    Selection.Copy
    Workbooks.Open Filename:="X:\OSC DETAIL\Invoices Sent to AP\Sent Invoice Summary - 2012.xlsx"
    Windows("Sent Invoice Summary - 2012.xlsx").Activate
    Sheets("Contractors").Select
    Rows("4:4").Select
    Selection.Insert Shift:=xlDown
    ActiveWorkbook.Close SaveChanges:=True
    Windows("OS Invoices.xlsm").Activate
    Selection.ClearContents
End Sub

---------- Post added at 04:28 PM ---------- Previous post was at 04:01 PM ----------

Is there no way to copy a variable range using VBA? Variable range meaning the number of rows in the range will vary from day to day. Surely it's possible to select a range of cells and then run a macro to copy/paste whatever range I've selected with my mouse? Is Selection.Copy not meant for this? Seems to half-way work

[shg]

Building on Joseph's suggestion,

Sub ContractorsTab()
    Const sFile As String = "X:\OSC DETAIL\Invoices Sent to AP\Sent Invoice Summary - 2012.xlsx"
    Dim rCopy As Range
    
    Set rCopy = Selection

    With Workbooks.Open(Filename:=sFile)
        rCopy.Copy
        .Worksheets("Contractors").Rows(4).Insert Shift:=xlDown
        .Close SaveChanges:=True
    End With

    rCopy.ClearContents
End Sub

[JosephP]

or maybe

Sub ContractorsTab()
    Const sFile As String = "X:\OSC DETAIL\Invoices Sent to AP\Sent Invoice Summary - 2012.xlsx"
    Dim rCopy As Range
    
    Set rCopy = Selection

    With Workbooks.Open(Filename:=sFile)
        rCopy.Copy
        .Worksheets("Contractors").Rows(4).Resize(rcopy.rows.count).Insert Shift:=xlDown
        .Close SaveChanges:=True
    End With

    rCopy.ClearContents
End Sub

[VBA FTW]

"The object invoked has disconnected from its clients"
I'm getting this error when I try both of your codes. Thanks for trying though

[JosephP]

please post a workbook-that is not normal.

one other thing: are you using option explicit at the top of your code modules? it can sometimes help with this sort of error (and is good practice anyway)

[VBA FTW]

Yes I'm now using O.E. on top of my module.

I'm going to try my code and the coding you 2 have provided on my computer at home this weekend to see if I can duplicate the error on a different system. As far as posting a workbook, here you go. I'll attach both the WB that contains the macro as well as the WB I'm trying to insert my copied rows to

[JosephP]

works ok in 2011. is that a table in the Contractors sheet? if so, you may need to add listrows to it (not sure why you have it defined to use entire rows either)

[VBA FTW]

Yes each tab has a table on it (or will once I'm done with this project)

The reason I'm inserting entire rows is because I need to insert my selection at the top of each sheet (table). Since the number of rows I'll be inserting is going to vary from day to day, inserting the rows and shifting the cells down seemed like the simplest way to accomplish this.

After running the macro, did you try entering new data into the OS Invoice WB, selecting your rows, then running the macro again? This is where the problem resides (on my computer at least)

BTW, I greatly appreciate your assistance on this

[JosephP]

my question was why is your table set up to use all the columns on the sheet? if you are going to work with tables you should really add listrows rather than just inserting rows into the sheet-it seems to be much more stable

[VBA FTW]

I see, well, this is something I need to rectify. Earlier today I was messing with my macro and rather than copying and inserting rows, I was trying to find a way to select just the range I need and copy/paste to the top of the table. In doing so, I apparently left something out of my code and the macro pasted my data into what seemed like infinite columns and extended my table to infinity and beyond (I should have removed my save, close code before running). It's not intentional and I plan on fixing when I have the time. I'll try using listrows and see if that helps, will post my results when I have a chance

[JosephP]

try something like

Sub ContractorsTab()
'
' ContractorsTab Macro
' Adds selected rows to 2012 Sent Invoice Summary Sheet
'

'
    Dim rCopy As Range
    Dim wb As Workbook
    Dim oList As ListObject
    Dim n As Long
    
    Set rCopy = Selection
    Set wb = Workbooks.Open(Filename:="C:\Sent Invoice Summary - 2012.xlsx")
    Set oList = wb.Sheets("Contractors").ListObjects(1)
    For n = 1 To rCopy.Rows.Count
        oList.ListRows.Add 1
    Next n
    rCopy.Copy oList.ListRows(1).Range.Cells(1)
    ActiveWorkbook.Close SaveChanges:=True
    rCopy.ClearContents
End Sub

[VBA FTW]

OK Joseph, I'm at home now and just tried my macro, and it works just fine on my computer here, so the problem seems to lie with our network at work. Any ideas at all on what could be causing this macro to not run properly? The code seems to be ok as it works on both of my home computers. I'll have to wait until Monday to see if your above code will work at the office

[VBA FTW]

I think the table is what is causing problems for me. I noticed today that if I copy my rows and right click on a row on the table, there isn't an "insert copied rows" option. I'm creating new workbooks to put my data in (without tables as I don't need them) and I just ran one of the macros out of and into the new workbooks and it seems to be working fine. I'm sure there is some rule here that I don't know about that isn't allowing me to insert copied rows, but whatever, if creating new workbooks does it, that's what I'll do. Thanks for the help everyone!