Using an iputted array across multiple worksheets
 

Hi, I've made a macro that ask for the user to select a range through the
message box, which then uses that range to calculate the sum of it and put it
on another workbook. This part works as expected. But what I'm trying to do
after that is use that same range (such as U51:U55) on another worksheet to
do the same thing. I'm sure I'm doing something stupid but I just can't find
anything that will let me do this. Here is pieces of the code:

On Error Resume Next
Set prevRow = Application.InputBox(Prompt:="Please select the range to
reference", Title:="Range selection", Type:=8)
On Error GoTo 0
If prevRow Is Nothing Then Exit Sub

moveIN = WorksheetFunction.Sum(prevRow)

This part works fine. I just need to do how to use that range on another
worksheet.

Using an iputted array across multiple worksheets
 

Maybe like this...

NextIN = WorksheetFunction.Sum(Worksheets("Sheet1").Range(p revRow.Address))

where you would change my Sheet1 example name to the actual name of your
worksheet.

--
Rick (MVP - Excel)


"Harry" wrote in message
...
Hi, I've made a macro that ask for the user to select a range through the
message box, which then uses that range to calculate the sum of it and put
it
on another workbook. This part works as expected. But what I'm trying to
do
after that is use that same range (such as U51:U55) on another worksheet
to
do the same thing. I'm sure I'm doing something stupid but I just can't
find
anything that will let me do this. Here is pieces of the code:

On Error Resume Next
Set prevRow = Application.InputBox(Prompt:="Please select the range to
reference", Title:="Range selection", Type:=8)
On Error GoTo 0
If prevRow Is Nothing Then Exit Sub

moveIN = WorksheetFunction.Sum(prevRow)

This part works fine. I just need to do how to use that range on another
worksheet.

Using an iputted array across multiple worksheets
 

See sample code - specifiy correct sheet name as required.

Sub test123()
Dim prevrow As Range
Dim moveIn As Double
On Error Resume Next
Set prevrow = Application.InputBox(Prompt:="Please select the
range to reference", Title:="Range selection", Type:=8)
On Error GoTo 0
If prevrow Is Nothing Then Exit Sub


moveIn = WorksheetFunction.Sum(prevrow)
Debug.Print "moveIn:", moveIn
Debug.Print prevrow.Address

moveIn = WorksheetFunction.Sum(Sheets("Other").Range
(prevrow.Address))

Debug.Print "moveIn:", moveIn

End Sub