Selecting a range defined in a cell
That worked great. Thanks for the help..
"CmK" wrote:
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
"Alex" wrote:
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?
"CmK" wrote:
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
"Alex" wrote:
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
"Tom Ogilvy" wrote:
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.
--
Regards,
Tom Ogilvy
"Alex" wrote:
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
|