Modification of a macro

  • Thread starter Thread starter Srinivasulu Bhattaram
  • Start date Start date
S

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



===============================================================
 
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
 
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
 
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



================================
 
Back
Top