command button to save and print

[Muascott]

hello all,
I am trying to write a macro for a button to do these tasks
1. save as workbook in a location say C:\My Documents\Msr's for numbers action.
2. use a range on the workbook for the title of the saved workbook say
"D4"
3. saved workbook to have prefix text of MSR F00xxx then Followed by the text in range D4
4. print the workbook
5. automatically close excel.

any ideas?

thanks

 

[Tazguy37]

Maybe something like this (in a standard module):

    With ActiveWorkbook
        .SaveAs "C:\My Documents\Msr's\MSR F00" & Range("D4").Value & ".xls"
        .PrintOut
        .Close 0
    End With
    Application.Quit

Hope that helps!

 

[Muascott]

how do i get it to just print the active sheet and not the workbook?

also the button closes the workbook but not excel can that be done?



thanks
scott

 

[Muascott]

how do i get it to just print and save the active sheet and not the workbook?

also the button closes the workbook but not excel can that be done?



thanks
scott

 

[Tazguy37]

You did say save the workbook and print the workbook in your original post. However, this should do the trick:

With Activesheet 
        .SaveAs "C:\My Documents\Msr's\MSR F00" & Range("D4").Value & ".xls" 
        .PrintOut 
    End With 
    Application.Quit

Application.Quit should close Excel. If you want to ignore any dialogs that might pop up during this operation, you could set Application.DisplayAlerts to False.

HTH

 

[Muascott]

thanks man,
I really need to read more.