Auto Open Macro

one_more_cave_dweller

Board Regular
Joined
Sep 27, 2004
Messages
118
:rolleyes:
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 :pray:
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,225
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.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,225
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

Board Regular
Joined
Sep 27, 2004
Messages
118

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,225
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

Board Regular
Joined
Sep 27, 2004
Messages
118

ADVERTISEMENT

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

Thanks
 

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
Try change the end part i.e., Range("C2") to:

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

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,328
Members
414,444
Latest member
lionking15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top