XL97 VBA Ranges!!!!

[vaneagle]

I'm stuck!!!
Background
I have a user form. Once it loads it prompts the user to select a department. Depending on the selection that the user makes for the department, it needs to select a certain range. IE if they select grocery then the range is B6:G16. It then copies that data range and pastes to a different location..well that's what it is supposed to do!!!

My Issue is that I can't seem to get the ranges to work. I am new to vba and I don't quite understand what I am doing wrong...

My code is as shown below:

Private Sub cmdOK_Click()
    Dim WB As Workbook
    Dim Dept As Variant
    Dim Source As Variant
    Dim RangeG As Range
    Dim RangeP As Range
    Dim RangeV As Range

    Set RangeG = Range("B6:G16")
    Set RangeP = Range("B18:G28")
    Set RangeV = Range("B18:G28")
    Set WB = Workbooks(Dir(ActiveWorkbook.FullName))
    Debug.Print WB.FullName

    WB.Activate
    Sheets("Sales").Select
    Range("j2").Select
    
    Dept = Range("j2")
    MsgBox "Your Dept is " & Dept & " Is this correct?", vbYesNo, "Department"
    If vbYes = 6 Then
    If cboDepartment = Dept Then
    Select Case cboDepartment
        Case "Grocery"
            Source = RangeG
        Case "Perishables"
            Source = RangeP
        Case "General Merchandise"
           Source = RangeV
Workbooks.Open FileName:="C:\Testing\JR DW Data.xls"
    Sheets("Sales").Select
    'Range("B6:G16").Select
    Source.Select
    Selection.Copy
    WB.Activate
Sheets("Sales").Select
    Range("B24").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Windows("JR DW Data.xls").Activate
    JunkData.SetText ""
    JunkData.PutInClipboard
    ActiveWorkbook.Close SaveChanges:=False
    End If ' end if for making sure cbodepartment = dept
    End If ' end if for if it is the wrong dept
    
End Sub

 

[xlbo]

try dimming Source as Range

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[combo]

vbYes is always 6, so change the dept selection formula to:

If MsgBox("Your Dept is " & Dept & " Is this correct?", vbYesNo, "Department&quot = vbYes Then

combo

 

[TonyJollans]

Hi vaneagle,

To make Source a valid Range Object you should use Set to assign it ..

Set

 Source = RangeG

etc

Enjoy,
Tony

 

[vaneagle]

Thanks for your input guys, but its still not working....

Dim WB As Workbook
    Dim Dept As Variant
    Dim Source As Range
    Dim RangeG As Range
    Dim RangeP As Range
    Dim JunkData As New DataObject ' Declare Variable to clear out clipboard
    Set RangeG = Range("B6:G16")
    Set RangeP = Range("B18:G28")
    Set RangeV = Range("B18:G28")
    
    Set WB = Workbooks(Dir(ActiveWorkbook.FullName))
    Debug.Print WB.FullName

    WB.Activate
    Sheets("Sales").Select
    Range("j2").Select
    
    Dept = Range("j2")
    
    If MsgBox("Your Dept is " & Dept & " Is this correct?", vbYesNo, "Department") = vbYes Then
    
    If cboDepartment = Dept Then
    
    Select Case cboDepartment
        Case "Grocery"
           Set Source = RangeG
        Case "Perishables"
            Source = RangeP
        Case "General Merchandise"
           Source = RangeV
       '  MsgBox "Your Dept is " & Source & " Is this correct?", vbYesNo, "Department"
    
    End Select

    Workbooks.Open FileName:="C:\Testing\JR DW Data.xls"
    Sheets("Sales").Select
    'Range("B6:G16").Select
    Source.Select
    Selection.Copy
    WB.Activate
    Sheets("Sales").Select
    Range("B24").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Windows("JR DW Data.xls").Activate
    JunkData.SetText ""
    JunkData.PutInClipboard
    ActiveWorkbook.Close SaveChanges:=False
    End If ' end if for making sure cbodepartment = dept
    End If
    
End Sub

When I:

 dim source as variant, Source = RangeV

the

source.select

produces an 'Object Required' Error.

When I:

 dim source as variant, set source = RangeG

the

source.select

produces a Select Method of Range class failed
or:

 dim source as range, set source = RangeG

the

source.select

produces a Select Method of Range class failed

Any suggestions ??

 

[TonyJollans]

Hi vaneagle,

When you try and do Source.Select you have opened a new workbook and selected a new sheet. It is unclear (to me, and to Excel) which Sheet in which Workbook you are referring to.

Enjoy,
Tony

 

[Loomah]

Hi vaneagle
To add to what Tony has just said, is "Sales" in the workbook you opened a worksheet? The newly opened book becomes the active book and excel transfers processing to the active object unless told to do otherwise.

What happens if you try to select a range, ie if you uncomment the line above source.select?

I was also wondering why assign the range to a variable then assign that variable to another variable (source). Why not assign the actual ranges to source in your select case statements?

Happy zitty nose Friday!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Loomah]

Hi again
In addition to the suggestions/questions above what happens if you replaced everything after opening the "Data" workbook down to and including the line where you close it with the code below.

ie
workbooks.open...
newcode
end if

With ActiveWorkbook
    .Worksheets("Sales").Source.Copy _
        Destination:=WB.Worksheets("Sales").Range("B24")
    .Close False
End With

Funtime!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[vaneagle]

Hi Tony and Loomah,
Thanks for your help and questions so far.....
The file where the form sits is already open when it starts, so it knows which workbook it is in to start with. In this case the name is called template.xls. So if I am correct the wb should reference to this template.xls.
"Sales" is a sheet within both the source and destination files.

If I uncomment

'Range("B6:G16").Select

and comment

 'source.select

it picks up the range specified. and pastes it to where it needs to go. No Issue.

I guess what I am trying to achieve is that if the user select's a different department then I need to pick up a different range. So I thought if I could set it up so that if the range selected, depending on the department they selected, was flexible then I could use the case statement to get around my issue!!

Does this make it a bit clearer in what I am trying to achieve?

 

[combo]

Hi vaneagle,
It seems to me that the Source object is Nothing, I have not found anything like Set Source=..., so this is why you have "object required" message.
You could speed, simplify and protect your code using fixed referencing and avoid statements like "Activate", "Select". This an example:

[tt]Set wb2=Workbooks.Open FileName:="C:\Testing\JR DW Data.xls"
wb2.Sheets("Sales&quot.Range("B6:G16&quot.Copy
WB.Sheets("Sales&quot.Range("B24&quot.PasteSpecial Paste:=xlValues, SkipBlanks:=False[/tt]

instead of (I ommited Source.Select):

[tt]Workbooks.Open FileName:="C:\Testing\JR DW Data.xls"
Sheets("Sales&quot.Select
'Range("B6:G16&quot.Select
Source.Select
Selection.Copy
WB.Activate
Sheets("Sales&quot.Select
Range("B24&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False[/tt]

combo

 

[vaneagle]

Thanks for your helps guys.... I am going to try another tack....