The input box will return a text string
Dim TextRng as string
Set TextRng = Application.InputBox (Prompt:="Range to copy:", Type:=8)
'for example, B3:B27

Set CopyRng = sh.Range(TextRng)

Aircode, but that should give you a good start. You may want to put in an
error checker, in case the entry doesn't correspond to a valid range
("A43A54" or "G7:G")

HTH
Keith

"ScottMsp" wrote:

Hello,

I am trying to modify Ron Debruin's CopyRangeFromMultiWorksheets macro.
Specifically I want the macro to prompt the user for a range vs. going in and
tweaking the macro each time based on what range is needed.

For instance, the user will first need to consolidate range A6:C11 from 50+
worksheets. The next time they will need to consolidate A16:C22 from the
same set.

The code that Ron has is
'Fill in the range that you want to copy
Set CopyRng = sh.Range("b6:C11")

I tried to use different variations of the following:
Set CopyRng = Application.InputBox _
(Prompt:="Range to copy:", Type:=8)

The macro fails.

Thanks in advance for helping me modify this macro.