saving a workbook, naming it and saving it to a specific path

[MJKelly]

Hello,

How do I create a new workbook containing two worksheets from an
active spreadsheet and have the new file saved in a specific folder
and also name it using a value from a cell in the original
spreadsheet?

i.e.
Copy two worksheets "sheetB" and "sheetC"
create a new workbook
save the filename as = cell A1 from "sheetA"
save the file to path = E:\Work Files\ArrivalsData\

hope someone can help,
Matt

 

[barnabel]

Whenever you have something like this where you know all the steps, the best
place to start is to use the macro recorder to record the steps and then edit
it.

After a quick record and edit, here is a macro that will do what you ask.
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/19/2007 by Peter Richardson
'
Dim SourceBook As String
Dim TargetBook As String
Dim fname As String

'
SourceBook = ActiveWorkbook.Name

Workbooks.Add
TargetBook = ActiveWorkbook.Name
Workbooks(SourceBook).Sheets("SheetB").Copy
Before:=Workbooks(TargetBook).Sheets(1)
Workbooks(SourceBook).Sheets("SheetC").Copy
Before:=Workbooks(TargetBook).Sheets(2)
fname = "E:\Work Files\ArrivalsData" +
Workbook(SourceBook).Sheets("SheetA").Cells(1, 1)
Workbooks(TargetBook).SaveAs Filename:= _
fname, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub

 

[MJKelly]

barnabel

Thanks loads for this. I have played around a bit and now have
exactly what I need to create 14 new files complete with specified
names. Cheers for the help on a Sunday night too.

Matt