save nonactive worksheet as a seperate workbook?

[Dwayne]

Hi, I'm trying to have a non active worksheet save to a new workbook. I
am able to do what I need by using the activeworkbook command, but I
end up creating new workbooks with more worksheets then they need. What
I am doing is creating packing slips with Excel, on one sheet the user
enters the data as well as drop down lists, then each time I execute
the print macro I created, it copies the data from the user form to
where I want them on the packing slip, increments the serial number
for the packing slip, prints it, then saves a copy of the packing slip
using the packing slip number which is located in a cell as the file
name. It's working okay, I just want to be able to save the actual
packing slip file without the other tabs that have the user entered
data. I will probably turn the user portion into a form, but for now
this solution is working..minus being able to save a non active
worksheet as its own file.

Thanks to all for any help.

 

[Bob Phillips]

Worksheets("Sheet3").Move

This will create a new, single sheet workbook, which you can save.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bernie Deitrick]

Dwayne,

Assuming your sheet is named "Packing Slip"


Dim myName As String
myName = "Packing Slip " & Worksheets("Packing Slip").Range("A1").Value & ".xls"
ThisWorkbook.Worksheets("Packing Slip").Copy
ActiveWorkbook.SaveAs myName


HTH,
Bernie
MS Excel MVP

 

[Dwayne]

Thanks Bernie, that did the trick!

Dwayne,

Assuming your sheet is named "Packing Slip"


Dim myName As String
myName = "Packing Slip " & Worksheets("Packing Slip").Range("A1").Value & ".xls"
ThisWorkbook.Worksheets("Packing Slip").Copy
ActiveWorkbook.SaveAs myName


HTH,
Bernie
MS Excel MVP