Open workbook, insert copied rows VBA code

**VBA FTW** · 07-26-2012, 04:59 PM

Hey everyone, this is my first post here, probably not my last

I'm trying to write a simple macro to copy selected rows from one workbook and insert the copied rows into another workbook. The rows I need to select and copy will vary from day to day. Here's my code so far:

Please Login or Register  to view this content.

My problem is the first line. Without it, this macro works fine if I already have the workbook I need to copy to open, but I'm trying to insert a little more code so the macro will automatically open up the workbook I'm inserting the copied rows to, then save and close. Obviously I haven't gotten to the save, close part yet, but I'm wondering if anyone could give me a little guidance on what is causing my error with the code above. The workbook opens fine with the above code, but then I get an error saying the "Selection.Insert Shift:=xlDown" line is not allowed because it is attempting to shift cells in a table on my worksheet. Why am I getting this error? The code worked flawlessly before I added the first line of code telling Excel to open my Sent Invoice Summary - 2012 workbook. Ever since adding in that piece of code, it isn't allowing the insert rows line to function. Anyone know what I'm missing or need to adjust? Thanks!

-Eric

**xladept** · 07-26-2012, 06:32 PM

Hey VBA FTW,

When a book is just opened, nothing is selected - if you want to copy the sheet that opens, you could write:

Please Login or Register  to view this content.

and you could write:

Please Login or Register  to view this content.

instead of:

Please Login or Register  to view this content.

instead of:

Please Login or Register  to view this content.

**VBA FTW** · 07-26-2012, 06:55 PM

When I open the book, I am already selecting the "Contractors" sheet with

Please Login or Register  to view this content.

and then selecting row 4 of that sheet with

Please Login or Register  to view this content.

The

Please Login or Register  to view this content.

is copying what I have selected from the original workbook. Since the numbers of rows I need to transfer will differ every day, I need to select them with my mouse before enacting the macro...so I'm telling excel to copy my selection with that

I'm not entirely convinced this is the issue, I won't be able to try your suggestion until tomorrow though

**xladept** · 07-26-2012, 07:04 PM

When you open the book, what do you expect to already be selected?

**VBA FTW** · 07-26-2012, 07:15 PM

I see your point, but aren't I already selecting Row 4 of the Contractors sheet when I open the book with the code in my above post? Or are you saying I need to select a cell, then select the sheet I want, then the row I want? I really appreciate your input

**xladept** · 07-26-2012, 07:18 PM

Yeah-but what's on the clipboard in the first place-you're only going to insert at row 4 but what you insert is what's on the clipboard!

**JosephP** · 07-26-2012, 07:23 PM

maybe

Please Login or Register  to view this content.

**VBA FTW** · 07-27-2012, 05:23 PM

Originally Posted by xladept

Yeah-but what's on the clipboard in the first place-you're only going to insert at row 4 but what you insert is what's on the clipboard!

Thank you so much. I'm pretty certain you are correct, there was nothing on the clipboard to paste to the new workbook once opened. I fixed it with by putting

Please Login or Register  to view this content.

before

Please Login or Register  to view this content.

Works like a charm now. Thanks again

**Cutter** · 07-27-2012, 06:35 PM

@ VBA FTW

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.