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.

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.

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

"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.

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




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.

--

Dave Peterson