Workbook macros

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
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


Code:
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

Well-known Member
Joined
Jan 6, 2003
Messages
1,941
What code do you have in the CVL.xls file? Maybe you only need to combine them in the master file.
 

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,941
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
 

Forum statistics

Threads
1,137,335
Messages
5,680,887
Members
419,937
Latest member
Talic

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