Auto Open Macro

[one_more_cave_dweller]

I need a macro that when I open book1 will copy sheet 1 b2:b10, open book2 and paste that data into sheet 3 c2:c10 then close book 2. Please help.

Thanks ray:

 

[Tom Urtis]

Any decision on this?
http://www.mrexcel.com/board2/viewtopic.php?t=128670


Try this:


Place this in the Book1 workbook module and see if it accomplishes what you are after. To easily access your workbook module, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedure into the large white area that is the workbook module. Press Alt+Q to return to the worksheet.

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Workbooks.Open ("C:\Your\File\Path\Book2.xls")
ThisWorkbook.Worksheets("Sheet1").Range("B2:B10").Copy Range("C2")
ActiveWorkbook.Close True
Application.ScreenUpdating = True
End Sub


Modify for path.

 

[one_more_cave_dweller]

Tom - Am I supposed to change anything in this line:
ThisWorkbook.Worksheets("Sheet 1").Range("B2:B10").Copy Range("C2")
I get a Run-Time error '9' message and when I debug that line is highlighted. Also I posted a reply at http://www.mrexcel.com/board2/viewtopic.php?t=128670

Thanks

 

[Tom Urtis]

The answer depends on what the source sheet tab's name really is.

In my code I named it "Sheet1", and in your follow-up email here you name it "Sheet 1" (notice you placed a space between "Sheet" and "1"). Take a close look at the spelling and all the characters (including spaces) in the source sheet tab (where you are copying from) and modify the code accordingly.

Thanks for following up on that other thread.

 

[one_more_cave_dweller]

I took out the space but everything else is right. Should there be a paste command and/or another reference to the target workbook.

Thanks

 

[Tom Urtis]

I just now tested my code again and it works fine, so there is something else going on with one or both of your workbooks. Maybe your workbook "Book2.xls" is not really named that, or really there is still a spelling issue with Sheet1 of the source workbook, or you have the code in the wrong workbook (it should not be in Book2 but in the workbook being opened first) or maybe you placed the code in the wrong module, not the Workbook module, or something else, I just cannot say.

In answer to your copy and paste syntax question, this line
ThisWorkbook.Worksheets("Sheet1").Range("B2:B10").Copy Range("C2")
is the copy and paste action; it is one way to code those instructions on one line.

 

[one_more_cave_dweller]

I have it in the ThisWorkbook module of Book1, I double checked the spelling and the path. Any other thoughts?

Thanks

 

[one_more_cave_dweller]

Can Anyone Help

 

[P Sitaram]

Try change the end part i.e., Range("C2") to:

ActiveWorkbook.Worksheets("Sheet3").Range("C2")

 

[one_more_cave_dweller]

Thanks Psitaram that fixed it!!!