VBA question, copying a worksheet to a different workbook

[aallaman]

Hi Everyone,

I am a real beginner when it comes to VBA so I need your help! I am reading Mr Excel's book on VBA and Macros but I cannot find a solution to the problem below.


Any suggestions on how to do the following?

1. The macro will be applied to an open workbook, lets call that WORKBOOK(*), as the name applies the workbook name will change between uses.
2. One of the first steps is to open a workbook off the network that contains a reference sheet, WORKBOOK2, this I can do
3. In WORKBOOK2 is the worksheet called REFSHEET
4. I need to copy REFSHEET into WORKBOOK(*)

I know how to write the VBA that could copy REFSHEET from WORKBOOK2 to lets say WORKBOOK1, but then if I want to run the same code on WORKBOOK55, the code wouldn't work.

Is there a way to identify the workbook in which you start the macro as a variable, and then reference that variable in the copy worksheet code? That way the code should work no matter what WORKBOOK(*) is named.

Thanks for your help with this!!!!

Allen

 

[VoG]

Maybe

Sheets("REFSHEET").copy after:=thisworkbook.sheets(sheets.count)

 

[aallaman]

Hey VoG,

You got me on the right track. This is what ended up working.

Sheets("Ref Sheet").Copy After:=Workbooks(ThisWorkbook).Sheets(1)

Thanks!

Allen