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
Application.ScreenUpdating = True
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
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.
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
is the copy and paste action; it is one way to code those instructions on one line.