Macro to print selected area

Hi, I am trying to create a macro where after selecting a named range, it would print the selected range. I have a worksheet divided in different ranges formated for printing. Each group of cell is named in order to make selection easier. I would like to create a macro where either excel prompts the user to select the named ranges, or cell ranges, and then print, or, after selecting a named range, it prints. I use Excel 2010. I tried to create it using the macro recorder, but it doesn't work. Any sugestions?

This should print the selected range.

Please Login or Register  to view this content.

Thanks AlphaFrog, I finally did that, because I erased part of the code, after running the macro recorder. But it still wonder if I can make excel prompt to ask for a range. I mean, get a dialog box (i think it is an input box) with a message "Enter range name or cells", the user enter the range name or cells and it prints the selected range.

Thanks AlphaFrog, it worked perfect! Now, I have a final question, somehow related. I want to do a macro, where excel promts to enter a range, but for a function. Lets say, for SUM, or AVG, or COUNT,

Thank you very much for all the help

You're welcome.

Your new request is too vague. I don't know how to code "Lets say". You need to be very specific; Prompt to select the range, Sum the selection, then do what with the sum?

The code would be nearly identical to the Print code above except instead of print, you do something else. Let's say Sum.

Sorry for the vagueness, I'm very new in this programming thing. I tried this:

"Sub Prompt_to_Select_SUM_Range()


Dim rng As Range, strRng As String

Do

strRng = Application.InputBox("Type in a Named Range or a cell range. ", "Select a Range to SUM", "A1:B20", Type:=2)

If strRng = "False" Then Exit Sub 'User canceled

On Error Resume Next
Set rng = Range(strRng)
On Error GoTo 0

If rng Is Nothing Then MsgBox "Cannot define '" & strRng & "' as a range. ", , "Invalid Range"

Loop Until Not rng Is Nothing

'rng.PrintPreview
'rng.PrintOut Copies:=1, Collate:=True
'ActiveSheet.DisplayAutomaticPageBreaks = False
rng.Calculate
End Sub"

But it didn't work, I suppose that one of my problems is the rng.Calculate statement, that it may not make sense to VBA, but what I want to do with the sum (average or any other simple function) is to display the result.

Thank you, I hope this is more specific.

Replace...
rng.Calculate

With this...
MsgBox Application.WorksheetFunction.Sum(rng), , "SUM"

I replaced the rng.Calculate with the statement you posted, but it gives me an error that says "Cannot define '500' (first number on the first cell of the range I'm writing) as a range.

Please Login or Register  to view this content.

Thank you, it worked. No more questions

how do i get the selected range to now fit to one page?

is there a way to select multiple ranges to print?

Originally Posted by AlphaFrog

Please Login or Register  to view this content.