Workbook macros

[JamieDuncan]

Ok this is a bit of a challenge this one, im trying to write a macro that when run;

1. Opens an existing workbook
2. Lets the newly opened book run its workbook_open macros
3. then copies a sheet from the original workbook into the newly opened one(sheets will have the same name)
4. Closes the original workbook

but wait theres a twist,

the macros in the second workbooks open event create a new workbook with filename entered into an inputbox, so the macro in the first workbook would need to copy the sheet to this newly created workbook.

help on this would be much appreciated

 

[JamieDuncan]

Any ideas?

 

[btadams]

Have you generated any code yet?

 

[JamieDuncan]

All I have so far,

MASTER is the starting file which has the macro,
CVL is the file it opens
CVL asks for a ref number in an inputbox
in this case the input was 12345-12-06
creating 12345-12-06MASTER
then pasting the range into the new file

Sub Macro1()
    Workbooks.Open Filename:="O:\Internal\CVL.xls"
    Windows("MASTER.xls").Activate
    Sheets("Input").Range("B4:U163").Select
    Selection.Copy
    Windows("12345-12-06MASTER.xls").Activate
    Sheets("Input").Range("B4").Select
    ActiveSheet.Paste
    Windows("MASTER.xls").Close
End Sub

What i dont know how to do is get the number from the new workbook.
hmm just remembered that the ref number enters itself into cell J8 on sheet DATA in the new workbook.

 

[btadams]

What code do you have in the CVL.xls file? Maybe you only need to combine them in the master file.

 

[btadams]

After thinking a little more, maybe this would do what you want:

Sub Macro1()
Dim strRefNum as String

Workbooks.Open Filename:="O:\Internal\CVL.xls"
Windows("MASTER.xls").Activate
Sheets("Input").Range("B4:U163").Select
Selection.Copy
Windows("12345-12-06MASTER.xls").Activate
Sheets("Input").Range("B4").Select
ActiveSheet.Paste
strRefNum = Workbooks("12345-12-06MASTER.xls").Sheets("Data").Range("J8").Value
Windows("MASTER.xls").Close
End Sub