Modify Ron Debruin Macro - Prompting for range

[ScottMsp]

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.

 

[ker_01]

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

 

[EricG]

How about using the refEdit control on a user form? Pop it up, let the user
select the range, and then grab the range text string from the refEdit
control to use in Ron's logic.

HTH,

Eric

 

[Dave Peterson]

Dim CopyRng as range

set copyrng = nothing
on error resume next
set copyrng = application.inputbox(Prompt:="Select something", type:=8)
on error goto 0

if copyrng is nothing then
msgbox "user hit cancel, what should happen here?"
exit sub '????
end if