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

M

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
 
B

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top