User Selected Range Across Workbooks

[Mountainman141]

Still kinda new at this.
I am trying to write a sub that will allow the user to select a range and a collection of files. That range will be pulled from each of the files selected and then pasted to a new workbook (same sheet). I will also be doing the same thing but instead to one sheet give each file its own sheet. I have a problem with the range. When I grab the range from the user, I can immediately select that range in the open worksheet. However, when I move over to the next workbook, it seems as if its out of scope or something. The error says object required. I have stripped the code down as much as I can to the part that is causing the problem.

Public userRange As Range

Sub test1()
fn = GetUserFiles()
Workbooks.Open fn(1)
Set temp = ActiveWorkbook
Set userRange = GetUserRange()
userRange.Select
temp.Close
Workbooks.Open "C:\test.xls"
Worksheets("Sheet1").Select
userRange.Select

End Sub

Function GetUserFiles()
fn = Application.GetOpenFilename("Excell Worksheets (*xls.),*.xls", , "Source Files", , True)
If TypeName(fn) = "Boolean" Then Exit Function
GetUserFiles = fn
End Function

Function GetUserRange() As Range
Application.ScreenUpdating = True
Dim oRangeSelected As Range
On Error Resume Next
Set oRangeSelected = Application.InputBox("Please select a range of cells!", _
"Select A Range", Selection.Address, , , , , 8)
If oRangeSelected Is Nothing Then
MsgBox "Action canceled!"
Exit Function
Else
Set GetUserRange = oRangeSelected
End If
Application.ScreenUpdating = False
End Function

 

[xenou]

You set the range...then immediately close the workbook that it is in. This effectively kills that range. You should not close the workbook until you are finished using the range object in it.

Set userRange = GetUserRange()
userRange.Select
temp.Close 'userRange is destroyed when workbook is closed.

If you are going to work in multiple workbooks, set an object reference to everything:

Dim wbSource as Workbook
Dim wbDestination as Workbook
Dim userRange as Range
Dim wbDestination as Workbook
Dim wsDestination as Worksheet

Set wbSource = ActiveWorkbook
set wsSource = wbSource.Sheets(1)
Set userRange = wsSource.Range("A1")
Set wbDestination = Workbooks.Open("C:\Test.xls")
set wsDestination = wbDestination.Sheets(1)

This might seem like too much but you'll find it makes it much easier to write your code as you refer variously to one workbook or worksheet or another. Having the worksheet references makes it easy to read and write on your sheets, and the workbook references make it easy to close the workbooks when finished, or if necessary to activate a workbook.

BTW the range object is usually enough to get what you need, but in some cases you'll need the worksheet too, which is why I suggest just getting in the habit of having references to your main objects (sheets, especially). Also, if you port the code to another project, it will be ready to be called by another sub that might pass a worksheet reference to it.

 

[Mountainman141]

Thanks for the response. Even when I leave my file open I still have problems. I guess I'm not sure how to reference the sheets the way you want. It just acts like it has no idea what userRange is! Here is what I'm running:

        Dim wbSource As Workbook
    Dim wbDestination As Workbook
    Dim userRange As Range
    'Dim wsDestination As Workbook
    Dim wsDestination As Worksheet
    
    fn = GetUserFiles()
    Workbooks.Open fn(1)
    Set wbSource = ActiveWorkbook
    Set wsSource = wbSource.Sheets(1)
    Set userRange = GetUserRange()
    userRange.Copy
    Set wbDestination = Workbooks.Add
    Set wsDestination = wbDestination.Sheets(1)
    wbDestination.SaveAs Filename:="C:\Documents and Settings\riebeekb\Desktop\Master.xls"
    Windows("Master.xls").Activate
    Sheets("Sheet1").Select
    userRange.Select

 

[xenou]

Sorry, I've been away for about 5 days. Did you fix this? Some tweaks:

Sub TestIt()
Dim userRange As Range
Dim wsDestination As Worksheet
Dim fn As String
    
fn = GetUserFile
If fn = "" Then
    Exit Sub
Else
    Workbooks.Open fn
End If

Set userRange = GetUserRange()

Set wbDestination = Workbooks.Add
Set wsDestination = wbDestination.Worksheets(1)
userRange.Copy
wsDestination.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
wbDestination.SaveAs Filename:="C:\myTemp\faceThegod.xls"
wbDestination.SaveAs Filename:="C:\Documents and Settings\riebeekb\Desktop\Master.xls"

End Sub
'--------------------------------
Function GetUserFile() As Variant
fn = Application.GetOpenFilename()
If TypeName(fn) = "Boolean" Then
    fn = ""
Else
    GetUserFile = fn
End If
End Function
'--------------------------------
Function GetUserRange() As Range
Dim oRangeSelected As Range

On Error Resume Next
Set oRangeSelected = Application.InputBox("Please select a range of cells!", _
"Select A Range", Selection.Address, , , , , 8)
If oRangeSelected Is Nothing Then
    MsgBox "Action canceled!"
Exit Function
Else
    Set GetUserRange = oRangeSelected
End If

End Function

Notes:
1. Your code was missing anything to tell you if no filename was returned - i.e., function exits but the main sub continued...

2. There was some problem with the use of the file extensions in the getopenfilename function. I just omitted all arguments (defaults to all file types)

3. Your code uses fn(1) but I'm not sure how this was meant to work. I am assuming the return value of the function will be a single string value (one file) and use fn instead of fn(1) in the main sub.

4. I don't actually see you doing anything with usedRange. You copy it...what else? I added a line to paste it into the new sheet.

HTH

 

[Mountainman141]

Ya, I got everything working. Thanks! Where I have problems now is if the user selects a varied Range. For example if they select Range(A1:A5,C1:C10) and then try to copy it.
I notice that this is something that can't be done even by a user running excel normally (selecting varying ranges, with a mouse the old fashion way, and trying to copy it). I still need to do some research on this one, sorry if its a common question. Any ideas? Is there a way to break up the range from the user?