saving with a macro.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a quote system, and all the code below is working and the file saves
correctly. You'll see the code copies the quote into into a new sheet and
saves it. Is there any way I can simply save the current sheet, because with
the current code, I lose all the formatting, and images in the sheet like the
company logo. I'd rather just save the current sheet and retain the
formatting. Would anyone be able to advise how I can re-code this.


Sub MacCreatePO()

Dim n As Range
Dim s As Range
Dim d As Range
Set n = Range("C4")
Set s = Range("B12")
Set d = Range("B16")
Application.CutCopyMode = False
Range("A1:C70").Select
Selection.Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteAll
ActiveWorkbook.SaveAs Filename:="C:\Documents and
Settings\Administrator\Desktop\dravings\" & n & " " & s & ".xls"
ActiveWorkbook.Close
Cells(1, 1).Select
MsgBox ("Quotation for " & s & " was saved as Quote " & n & " " & s &
".xls")
n.Value = n.Value + 1
ActiveWorkbook.Save

End Sub
 
Hi Jonathan,

Try something like:

'=============>>
Public Sub Tester()
Dim Rng As Range
Dim s As String
Dim d As String
Dim n As String

Set Rng = Range("C4")
n = Rng.Value
s = Range("B12").Value
d = Range("B16").Value
ActiveSheet.Copy
With ActiveWorkbook
.SaveAs Filename:= _
"C:\Documents and Settings\" _
& "Administrator\Desktop\dravings\" _
& n & " " & s & ".xls"
.Close
End With

MsgBox ("Quotation for " & s _
& " was saved as Quote " _
& n & " " & s & ".xls")
Rng.Value = Rng.Value + 1
ActiveWorkbook.Save

End Sub
'<<=============
 

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

Back
Top