Copy Worksheet From One Workbook To Another Workbook

[inte2000]

Hello all,

First off, I'm *very* new to macros, so please go easy on me. Thanks!

Here's the deal: I need a macro that copies one worksheet (from a workbook containing only 1 worksheet) named "CME Case Questions" and pastes into another (existing) workbook that has several worksheets already. The names of both workbooks will change from day to day, however. I then need both workbooks to be saved in the their respective directories..

Thanks in advance,
David

[shg]

 ' put copy after last sheet
Workbooks("CME Case Questions").Sheets(1).Copy _ 
After:=Workbooks("OtherWorkBook").Sheets.Count 
 
 ' put copy before first sheet
Workbooks("CME Case Questions").Sheets("Sheet1").Copy _ 
Before:=Workbooks("OtherWorkBook").Sheets(1) 
 
 ' copy before (or after) some particular sheet
Workbooks("CME Case Questions").Sheets("Sheet1").Copy _ 
Before:=Workbooks("OtherWorkBook").Sheets("SheetName") 

[inte2000]

Thanks--I appreciate the help, but is there a generic code for Workbooks("OtherWorkBook")?? The name of the workbook I will be copying to (and from) will be changing hourly..

Is there any way I can use a macro to copy a worksheet from one active workbook into another inactive workbook, regardless of the workbook's name?

Thanks again for the help!

David

[shg]

is there a generic code for Workbooks("OtherWorkBook")??

Use a variable:

Dim strWB As String 
 ' open this month's workbook (e.g., Revenue_Apr07)
strWB = "Revenue_" & format(today, "mmmyy") 
Workbooks.Open(strWB) 
With Workbooks(strWB) 
    .Sheet1.Range("A1") = Hello World" 
    ...