Going to a "set" range

[wpreqq99]

‘User clicks a button in UserForm5 (UF5) to choose a vehicle to edit.
Each vehicle is in it’s own sheet, V#1, V#2, V#3, etc. User is taken
to UF9 to make edit changes. When editing is pasted to proper sheet
(V#1 in example below), I want to go to the sheet Vehicle Summary,
call the range indicated below in the UF5 code, and paste the rest of
the changes from the UF9 edit.

This is in UF5
Private Sub CommandButton1_Click()
'Edit V #1

‘Dim and Set here is attempt to put range “V__1” into memory to call
later in UF9
Dim rng As Range
Set rng = Worksheets("Vehicle Summary").Range("V__1")
‘NOTE: I’ve also tried putting the Dim statement above in the
Declarations section. Also didn’t work.

‘This will bring up UF9 so that user can edit info, which is in sheet
V#1
Sheets("V #1").Select
Application.Goto Range("A1"), True
Unload UserForm5
UserForm9.TextBox1.SetFocus
UserForm9.Show
End Sub

‘In UF9, this command fills textboxes with correct info for user to
edit.
Private Sub Userform_Activate()
'Populates the fields
ShowToEdit
End Sub

‘After form is populated to show vehicle chosen before, user makes
changes, then clicks this button, which will cause the changes made to
the proper vehicle sheet. That code is also in UF9.

Private Sub CommandButton5_Click()
EditVehicle 'goes to code below to paste changes
End Sub

‘At this point everything works fine. The correct changes are made to
the proper sheet. All the changes come out correctly. What I want it
to do now is to continue by going to sheet Vehicle Summary, then go to
the range name indicated above (V#1 in this example). There is more
instructions after this that will work if I get to the range.


‘This is at the end of the Pasting routine in UF9. It takes me to
the correct sheet, but errors out in the rng.Active line.

Sheets("Vehicle summary").Select
rng.Activate ‘THIS LINE is highlighted with the error. I’ve also
tried different ways of ‘calling the range, with no success.


Obviously, I’m not doing the Dim/Set statements correctly. I can’t
make the range settings in UF9, because I want this to be generic code
for all the vehicles to use rather than to write it out 10 times. I’ve
searched all day for examples here, but I don’t see anything that
applies to what I’m doing. Any help will be appreciated.
Thanks
jeff

 

[Patrick Molloy]

you can't activate a range that's not on the active sheet. you probably
don't need to either.

to populate a cell with data

worksheet("x").Range("A1") = worksheets("b").Range("C2").value

or, with your code, something like this

rng.value = worksheets("v#2").Range("V__2").Value

 

[wpreqq99]

you can't activate a range that's not on the active sheet. you probably
don't need to either.

to populate a cell with data

worksheet("x").Range("A1") = worksheets("b").Range("C2").value

or, with your code, something like this

rng.value = worksheets("v#2").Range("V__2").Value

Ok, your saying I don't use a Dim statement. Let's say I use this
code.
rng.value = worksheets("v#2").Range("V__2").Value
I assumme I put this in UF5 ?

Then, in UF9, how do I "goto" this range?
something like??
Sheets("Vehicle summary").Select
rng.Activate

 

[wpreqq99]

Ok, your saying I don't use a Dim statement. Let's say I use this
code.
rng.value = worksheets("v#2").Range("V__2").Value
I assumme I put this in UF5 ?

Then, in UF9, how do I  "goto" this range?
something like??
Sheets("Vehicle summary").Select
rng.Activate- Hide quoted text -

I played around with this and got it. FYI, I had to make a Public
declaration in a module, and move the statements I had in the UF5 to
this module. I always struggle with the memory issues dealing with
Dim, Set, etc.
Thanks for the help.
jeff