Save worksheet to a unique filename after worksheet is printed

L

Lifestyle

I have a worksheet where cell "B1" automatically generates a unique
number (i.e. like an invoice number). I would like to have a command
button (as well using the File|Print) to print the worksheet as well as
saving the worksheet to a unique filename which includes whatever is in
cell A1 and A2 as well as the date that it was printed. i.e. A1 = PAS,
B1 = 001 then the filename should be PAS001_ddmmyyy.xls.

I do not want to save the whole workbook to the new filename but only
the individual worksheet.

Thank you
 
N

NickHK

Something like this :

Private Sub CommandButton1_Click()
Dim WB As Workbook
Dim FileName As String

With Worksheets(1)
.PrintOut
FileName = .Range("A1").Value & .Range("B1").Value & Format(Now(),
"ddmmyyy")
.Copy
End With

Set WB = ActiveWorkbook
WB.SaveAs FileName

End Sub

NickHK
 
O

okrob

May be a little more than you asked for, but you can always
remark/delete the stuff you don't need.
Rob

++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Sub MD()
On Error GoTo done
MkDir "C:\YourPath\"
'<=== Change "YourPath" to where you save the files
done:
End Sub

Sub wscopy()
Call MD
Dim SBookName
Dim tdate As String
tdate = Format(Date, "ddmmyyyy")

Dim rng1 As String, rng2 As String, rng3 as String
rng1 = Range("A1").Value
rng2 = Range("A2").Value
rng3 = Range("B1").Value

Dim FN As Variant
ActiveSheet.Copy
With Application
FN = .GetSaveAsFilename _
("C:\YourPath\" & rng1 & rng2 & rng3 & "_" & tdate & ".xls")
'<=== Change "YourPath" to where you save the files
If FN <> False Then
ActiveWorkbook.SaveAs FN
End If
End With
' Unmark the next line to attach the file to an email using your
default email client
' Application.Dialogs(xlDialogSendMail).Show

End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 
O

okrob

Sorry, on the wscopy sub, you don't need to dim SBookName
That was left over in my original workbook code.
Rob
 
M

Maddoktor

Thanks for your help ... works great.

Is it possible to save the worksheet in the background (don't want the
Save As dialog box to appear) and then print it to the default printer?
 
O

okrob

You bet... Forgot that you wanted to print it. NickHK had it on the
money... The default save location for excel is used though, not a
specific directory. In most cases, the default is the my documents
folder.
Also, I just didn't figure you wanted the format to actually be mmddyyy
This would give you a date of 18010718. Basically, the day, the
month, the 2 digit year, and the day again.
If that's the way you wanted it, I kinda took some liberty here and
changed it for you. Anyway, here's my updated version:

Sub wscopy()
Call MD
Dim tdate As String
tdate = Format(Date, "ddmmyyy")
Dim rng1 As String, rng2 As String, rng3 As String
rng1 = Range("A1").Value
rng2 = Range("A2").Value
rng3 = Range("B1").Value
Dim FN As Variant
With ActiveSheet
.Copy
.PrintOut
End With
With Application
FN = ("C:\YourPath\" & rng1 & rng2 & rng3 & "_" & tdate &
".xls")
'<=== Change "YourPath" to where you save the files
If FN <> False Then
ActiveWorkbook.SaveAs FN
End If
End With
' Unmark the next line to attach the file to an email using your
default email client
' Application.Dialogs(xlDialogSendMail).Show
End Sub
 
O

okrob

oops... I was trying the code out to see what the mmddyyy would get me
and I forgot to change it back before I copied it to here... Suggest
changing it to mmddyyyy for accurate date representation.
Rob
 
M

Maddoktor

Thanks Rob ... works great.

Can you tell me how close the new workbook automatically after printing
that was created.
 
O

okrob

Add this:
ActiveWorkbook.Close False

AFTER this line in the code:
ActiveWorkbook.SaveAs FN

Rob
 

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