Save worksheet to a unique filename after worksheet is printed

[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

 

[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

 

[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
++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

[okrob]

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

 

[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?

 

[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

 

[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

 

[Maddoktor]

Thanks Rob ... works great.

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

 

[okrob]

Add this:
ActiveWorkbook.Close False

AFTER this line in the code:
ActiveWorkbook.SaveAs FN

Rob

 

[Maddoktor]

Thank you Rob.

Add this:
ActiveWorkbook.Close False

AFTER this line in the code:
ActiveWorkbook.SaveAs FN

Rob