SaveAs code

[mcfly]

Is there a way to save the worksheet as a pdf or just a read-only file that doesn't include the macros.

And also save the filename as "Quote" plus a number so that everytime you save this quote it will add a number to your sheet and also to the SaveAs filename

here is whats at the end of my finish button code:

msg = "Would you like to save this Quote?"
ans = MsgBox(msg, vbQuestion + vbYesNo, appname)
If ans = vbYes Then ActiveSheet.PrintOut
If ans = vbYes Then Unload Me
If ans = vbYes Then activeWorkbook.SaveAs

and that is all I can figure out can anyone of the code gurus help me out?

So whenever I select yes it will printout my sheet and at the same time save as a seperate file(read-only or pdf) named "Quote 000001"
and then the next time "Quote 000002".

Thanks in Advance!!!

 

[JohnG]

You cant write a PDF file unless you have Adobe Acobate. Hers a suggestion on a save as routine.

Dim FileCount As Integer
Dim CurntBook As String
Dim Msg As String
Dim Ans
Dim SPath As String
Dim SFileName As String

Sub SaveIt()
'This requres a 2nd sheet to store the count
CurntBook = ActiveWorkbook.Name
SPath = "C:"
'get counter
If Workbook(CurntBook).Worksheet("Sheet2").Range("A1").Value < 1 Then
FileCount = 1
Else
FileCount = Workbook(CurntBook).Worksheet("Sheet2").Range("A1").Value
End If

Msg = "Would you like to save this Quote?"
Ans = MsgBox(Msg, vbQuestion + vbYesNo, appname)
If Ans = vbYes Then
Workbook(CurntBook).Worksheet("Sheet2").Range("A1").Value = FileCount 'write back counter
Sheets("Sheet1").Move 'to new workbook
SFileName = SPath & "Quote" & FileCount 'build filename
'save
ActiveWorkbook.SaveAs FileName:=SFileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=True, _
CreateBackup:=False
'print
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'close
ActiveWorkbook.Close
Windows(CurntBook).Activate
End If
End Sub

 

[mcfly]

I hate be such a vba rookie but I've tried placing this in my userform code in a module in this workbook and I can't get it to work.
In my userform code I get the error sub or function not defined and it Highlight workbook under 'get counter
This message was edited by mcfly on 2002-10-18 08:51

 

[JohnG]

Do you have a sheet2 in your master workbook, if not that will be the error.

 

[mcfly]

yes I have a sheet2 which is empty