Text to Columns Macro

[DaveRadford]

Afternoon All,

I have a macro or rather i recorded one for what i wanted to automate:

Columns("A:A").Select

Selection.TextToColumns Destination:=Range( _
"Table_owssvr_1[[#Headers],[Workstage]]"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True


May be a fairly silly questions, but, when i try and call this in a sub or even run the macro, it complains that i can only do one column at a time. .TestToColumns is what its called so i dont get it. I have tried the range being A:A etc.

Thanks,

 

[DaveRadford]

the reason for using the 'with sheets' is that the wsmaster sheet will be hidden from view meaning we cant select or activate it.

By using activate it would appear that it formats the active sheet :/

 

[Joe4]

Well, I think there's your problem right there. I don't think you can run TextToColumns on a hidden sheet. Trying unhiding your sheet, running the code, then rehiding the sheet. You can incorporate all of that in your VBA code. The macro recorder should get you all the code you need.

 

[DaveRadford]

the sheet isnt hidden wehn im attempting to run the macro/ sub its just that i need to use with sheets in order to run it whilst hidden.

 

[Joe4]

the sheet isnt hidden wehn im attempting to run the macro/ sub its just that i need to use with sheets in order to run it whilst hidden.

??? I think you need to clarify that statement a bit more.

Can you describe the scenario when the macro is run (status of sheets), and post your complete code?

 

[DaveRadford]

This sub onyl consists of what is already been posted, i have 1000's of lines of code so best i dont paste it in.

i wanted to use with sheets because i will eventually set to hidden, however at the moment its visible and unprotected and wont work with withsheets.

All i want it to do is format the data in different worksheet column A:A with text to columns function on A1 without having to move between sheets. I want to manipulate the data without having to switch in to the other worksheet.

Thanks,

 

[Joe4]

After further testing, it appears that TextToColumns may be more restrictive than we first thought. It appears to me that the sheet actually has to be the current active sheet for it to run against it. So it looks to me like you like using "With" will not work, you may need to actually Select/Activate the sheet you want to run it against.

Still, it shouldn't be a big deal to do that. You can program your macro to hide/unhide sheets, select sheets, etc without the user even knowing it.

 

[DaveRadford]

in that case then how can i record the last named worksheet so that i can then use that name.select to go back to the original form and set application.screenupdating = true was set to false so users can see it moved at all.

 

[Joe4]

When you are on the worksheet that you want to apply the code against, you can capture its name like this:

    Dim myWS As String
    myWS = ActiveSheet.Name

You can then come back to it later like this:

    Sheets(myWS).Activate

 

[Gettingbetter]

You could just use

With Sheets("Sheet1")
  .Range("A:A").NumberFormat = "@"
  .Range("A:A").Formula = Range("A:A").Formula
End With

instead of the TextToColumn way, you can use with sheets then

Cheers
GB