Selecting a range defined in a cell

[Guest]

I am trying to select a range that is defined in a cell on sheet 1. this is
what i have but am getting no place with it. any ideas?

ActiveWorkbook.Sheet1.Range.Value=("u4").Select

 

[Guest]

What i remember you have the activate the sheet before you select the cell
This formula
ActiveWorkbook.Sheet1.Range.Value=("u4").Select
Just wont select the cell
if you just want the value in thet cell u can make it a variable

Hope this helped

thanks

 

[Guest]

Assume A1 on Sheet1 holds the Text "U4" without the double quotes

Dim s as String
Wtih Activeworkbook.Worksheets("Sheet1")
s = .Range("A1")
Application.Goto .Range(s)
End with

If that isn't what you want, then perhaps providing a clearer explanation
would help.

 

[Guest]

How would I make it a variable? I am rather new to this...

 

[Guest]

Hi
Well that depends
Do you only want to select the cell(u4) if so then you have to activate the
sheet before you select the cell
If there is a value in the cell that you would want to use then you can make
it a variable
Just let me now what you are planning to do with the cell first

thanks hope it helped

 

[Guest]

I thought is best to post the whole string below.

What I am trying to do is select a portion of my sheet (defined in cell u4)
then open another workbook, Copy trhat data to it, Save the workbook using a
name defined in another cell and then close the newly created workbook.
Everything else in the below code works. I have used it a lot before. The
only problem is the first line to select a range based on the data in cell u4.

I hope this helps.

Private Sub CommandButton2_Click()

ActiveWorkbook.Sheet1.Range.value_("u4").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs "C:\" & ThisWorkbook.ActiveSheet.Range("u3") &
".xls" _
, FileFormat:=xlCSVMSDOS, CreateBackup:=False
ActiveWorkbook.Close
End Sub

 

[Guest]

Hi

try the one below see how you go thanks


Private Sub CommandButton2_Click()
Dim A as string

a = Range("U4").Value
Range(a).Activate

Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs "C:\" & ThisWorkbook.ActiveSheet.Range("u3") &
".xls" _
, FileFormat:=xlCSVMSDOS, CreateBackup:=False
ActiveWorkbook.Close
End Sub

 

[Guest]

I tried it as you wrote it and it got stuck on Range(a).activate..

I tried to pull that line just for kicks and it went back to making a file
with the only the values of U4 in it and not what those values refer to...

Any more idea's for me?

 

[Guest]

Sorry try this one
Select instead of activate
and shortened the vba a bit

Private Sub CommandButton2_Click()

Dim A As String

A = Range("U4").Value
Range(A).Select

Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlValues
ActiveWorkbook.SaveAs "C:\" & ThisWorkbook.ActiveSheet.Range("u3") _
& ".xls", FileFormat:=xlCSVMSDOS

ActiveWorkbook.Close

end sub

 

[Guest]

That worked great. Thanks for the help..

Sorry try this one
Select instead of activate
and shortened the vba a bit

Private Sub CommandButton2_Click()

Dim A As String

A = Range("U4").Value
Range(A).Select

Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlValues
ActiveWorkbook.SaveAs "C:\" & ThisWorkbook.ActiveSheet.Range("u3") _
& ".xls", FileFormat:=xlCSVMSDOS

ActiveWorkbook.Close

end sub