Saving worksheets to new workbook

[bigjim]

I want to save three worksheets in my main workbook to a new workbook to be
named from variables in "Main.xls". For Example: I have 3 worksheets in
"main.xls" called "ticket", "Job Report", and "W-15". I need to create a new
workbook named from the contents of cells A4 and K10 in worksheet "Calc" in
workbook "Main.xls" and then save copies of "ticket", "job report" and "w-15"
in this new workbook. So, if cell a4 had "AS011" in it and K10 had "Liberty"
in it, the new file would be named "As011Liberty.xls" and contain three
sheets named "ticket", "Job Report", and "W-15". I am working in Excell 2003
and this is what I have so far, but I can't seem to get it to work:

Dim strappend As String
Dim strpath As String
Dim str3 As String
Dim str4 As String
strappend = ActiveSheet.Range("a4")
strpath = "c:\field tickets\"
str3 = ActiveSheet.Range("k10")

fsavename = strpath & strappend & str3 & ".xls"
If Dir(fsavename) <> "" Then
fsavename = strpath & strappend & str3 & "a.xls"

End If
If Dir(fsavename) <> "" Then
fsavename = strpath & strappend & str3 & "b.xls"
End If

If Dir(fsavename) <> "" Then
fsavename = strpath & strappend & str3 & "c.xls"
End If

ActiveWorkbook.Sheets("Ticket").SaveAs fsavename
ActiveWorkbook.Close False

ActiveWorkbook.Sheets("Job Report").Select
Sheets("Job Report").Copy before:=Workbooks("fsname").Sheets("Ticket")

ActiveWorkbook.Sheets("W-15").Select
Sheets("W-15").Copy before:=Workbooks("fsname").Sheets("Job Report")

I would appreciate any help I could get.

Thanks,

Jim

 

[Spreadsheet Solutions]

Jim;

For a Dutchmen, it looked a little complicated, but in general, this is what
I usually do an what you could try.

Build a string based on the contents of cells A4 and K10 in worksheet "Calc"
in workbook "Main.xls".
It seems to me that you do so.
I usually call the string fName .

Delete the worksheets you don't need from the workbook your working on.
You could do so with Worksheets(1).delete where 1 is the index number for
the first worksheet.
You could also try Worksheets("Name").delete
Use DisplayAlerts; This prevents you from having to answer dialogs about
deleting sheets.
It will look a little like this:

Application.DisplayAlerts = False

Worksheets(1).delete
Worksheets("Name").delete
Application.DisplayAlerts = True

Save the rest of what is left with the new filename eg the string you build.

ThisWorkbook.SaveAs Filename:=fName

Now the old workbook isn't modified, but a new workbook is saved with the
name you specified and the sheets you need.

I hope I understood what you wrote and hope you'll find a solution in what I
described.

PS: Don't forget to create a back-up workbook. Testing is essential in al
circumstances !!

--

 

[bigjim]

So, if I understand you, your suggesting that I just delete all of the sheets
except the three I want in the new workbook, then save the original (after
deleting) to the new name. If I do this, I'll have the new one open and the
original one will close without being changed? Do you have any ideas about
how to keep the original one open and just saving the new one to a folder so
they can continue to work in the new one? If I can do that, I think this
will work. I'll give it a try.

Thanks,

Jim

 

[Spreadsheet Solutions]

Jim;

The later I did never try, but why not reopen that file ?

I made a lot of applications for T-Mobile where a thing you mentioned was
the crux.
I had a workbook containing many sheets with lots of data.
I had to do some hocus pocus to gather data on one final sheet and save, or
export this final sheet to a specific directory with a new workbook name.

Deleting all sheets except the final one and a save as command did my trick.
The original workbook remained intact.

If you want to, please e-mail to the address below so I can send you a empty
template to make this thing more transparant.
Please put "Template request" in the subject line....