Modification of a macro

[Srinivasulu Bhattaram]

This what I do for preparing an invoice for a routine job.

1. I created a Master file and write protected it.(which is a one time job)

2. When I want to prepare an invoice I open the master file enter the data ,
select the cells to print, save as with a different name, print the same
selection to a printer (Adobe is my printer).}
At the end of this process I get an spread sheet for the specific invoice
and a PDF file for the same.
I send the PDF file to my accounts department.

I created a macro using Record a Macro method.

It works fine

I want a small improvement in it.

2. When I run this macro, I have to provide the file name.

Is it not possible to create a file name using the contents of the file
(XLS) saved.

By that I mean .the file name should be

"Some Text"+(contents of cell of G15)+(contents of Cell H15)

In this particular case the file name will be

"Some Text 001 1st April 2008" (quotes not included)



Can any one help me to modify the macro?

seena

===============================================



Sub Procon_Bills_Save_As_and_Print()

'

' Procon_Bills_Save_As_and_Print Macro

' Macro recorded 01-09-2008 by Bhattarams

'



'

Range("B15:H48").Select

ActiveWorkbook.SaveAs Filename:= _

"M:\Bills\2008-2009\Bill Data Excel Files\Test.xls",
FileFormat:=xlNormal, _

Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _

CreateBackup:=False

ActiveWindow.ScrollRow = 5

Range("B15:H48").Select

Selection.PrintOut Copies:=1, Collate:=True

End Sub



===============================================================

 

[Roger Govier]

Hi

the following should do what you want

Sub Procon_Bills_Save_As_and_Print()
' Procon_Bills_Save_As_and_Print Macro
' Macro recorded 01-09-2008 by Bhattarams
Dim filename As String
filename = "Some text" & Range("G15").Value _
& Range("H15").Value & ".xls"

ActiveWorkbook.SaveAs filename:= _
"M:\Bills\2008-2009\Bill Data Excel Files\" & filename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

ActiveWindow.ScrollRow = 5
ActiveSheet.Range("B15:H48").PrintOut Copies:=1, Collate:=True

End Sub

 

[Srinivasulu Bhattaram]

Thank you for the help
When I copied this with necessary changes to the file locations I get a
compile error for this segmant of the macro
filename = "Some text" & Range("G15").Value _
& Range("H15").Value & ".xls"

If it has worked in your system...why it didn't in mine?
Can you look into it and let me know where I have messed up?
seenagoofed

 

[Rick Rothstein]

What else does it say in the Error Message Box besides "Compile Error"?

 

[Srinivasulu Bhattaram]

This potion of text apperas in red.

That's all.

I will send a screen shot.

If the list can not accept attachments, i may have to send the file off the
list

Seena



================================