Copying Column Selected into code

[Queryman]

I have a VBA routine that lets user select a column

Sub getcolumns()
Dim userRange As RANGE, output As String
    'get a range from the user via an input box

On Error Resume Next
    Set userRange = Application.InputBox( _
        prompt:="Select cells on a single column for processing", _
        Type:=8, Default:=Selection.Address)
    On Error GoTo 0
    'if no range selected, stop
    If userRange Is Nothing Then Exit Sub
    'if cells on more than one row selected, display message and stop

If userRange.Columns.Count > 1 Then
        MsgBox "Select just cells on a single column.  " & _
                "No action taken"
        'stop the macros
        End
        Else
        output = userRange.Columns.Address
        MsgBox "This is what you selected.  " & output & ""
    End If
End Sub

I then want to use the value of OUTPUT which is something like $A:A into another piece of code which splits the excel worksheet, so the following piece of code in RED
should be able to accept the value selected, if for example user selects column A, then OUTPUT would have $A:$A as it's value, I need to find a way to get that value into the code in red below, the first piece in red gets the whole value of OUTPUT while the second piece in red just gets the column name and the number 1, example A1.

I tried and tried and cannot get this to work, so any suggestions will be highly appreciated.

[COLOR=red]

ws1.Columns(output).Copy _[/color]
  Destination:=RANGE("IU1")
ws1.Columns("IU:IU").AdvancedFilter _
  Action:=xlFilterCopy, _
  CopyToRange:=RANGE("IV1"), Unique:=True
r = Cells(Rows.Count, "IV").End(xlUp).Row
[COLOR=red]RANGE("IU1").Value = RANGE("a1").Value
[/color]

Michael

 

[xlbo]

what error do you get on the 1st line ?? If it happens as you describe, I see no reason that it shouldn't work.

For the 2nd part if you only need part of the data, you just need some simple string manipulation - both RANGE() and COLUMNS() take a STRING srgument so I cannot actually see what the issue is...

Only thing I can think of is to ask how you are getting the "output" value into the 2nd piece of code - it seems to be a sub specific variable but would need to be GLOBAL to work properly and be transferred between subroutines.

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Queryman]

Hi Geoff,
Your reply promoted me to take another look at how I was getting the value in, and you were right about the GLOBAL part. So I can get that part to work, appreciate any elaboration you can give on converting say $A:$A to A1.
Thanks



Michael

 

[PHV]

A starting point:
MsgBox Cells(Range("$A:$A").Row, Range("$A:$A").Column).Address(False, False, xlA1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Queryman]

Thanks, I used the following
output2 = Mid(output, 2, 1) & 1



Michael

 

[PHV]

And what happens if output="$BA:$BA" ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Queryman]

Thanks for pointing that out.

Related question:
I am using the code to do the following:
I am trying to split a worksheet by a columns value and creating sheets out of each one, I have an option to keep them as separate tabs in the same workbook, or save each tab to a different workbook. The code I have works, the only two parts I am not happy with is what you just pointed out and number two is the option to save (usally there are hundreds of tabs), currently I have the path hardcoded in VBA like

.SaveAs "C:\temp\TEST\" & (c.Value) & ".xls"

to save each one of the tabs after it moves it from teh main sheet to the new tab

I found some code in this forum that helps bring up the dialog box to save like this

Dim strCurr As String
strCurr = CurDir()
strFilename = Application.GetSaveAsFilename("C:\TEMP\")
If strFilename = "" Or strFilename = "False" Then
End If

Is there a way to just get the name of the directory you want to save to in a variable using the code above, and I can then use it within my code like

.SaveAs "& SAVETODIR &" & (c.Value) & ".xls"
where SAVETODIR is derived the first time from dialog box

Michael

 

[PHV]

You may use this function:

Function PickFolder(strStartDir As Variant) As String
    Dim SA As Object, f As Object
    Set SA = CreateObject("Shell.Application")
    Set f = SA.BrowseForFolder(0, "Choose a folder", 0, strStartDir)
    If (Not f Is Nothing) Then
        PickFolder = f.Items.Item.path
    End If
    Set f = Nothing
    Set SA = Nothing
End Function

SAVETODIR = PickFolder("C:\TEMP")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Queryman]

Tnanks, I also found this thread
thread707-938499




Michael

 

[PHV]

Lol, seems I'm consistent in my replies ...