Text to Columns, continuously

I'm creating a template for others to use to process data that's hand-pasted in from another table in a .pdf file.

When I first open Excel and go to past in my table, it of course tries to put all the columns into a single cell. I can use the Text to Columns function to separate them out, and (jn Excel 2007) subsequent paste-ins of other tables automatically paste in correctly, which is nice. This continues even if I close the worksheet I'm working in an open a new one, but this behavior does not continue if I close out of Excel completely and re-open.

Because I want the worksheet I'm creating to be a template that others can use, is there a way for me to set it up so that the other users don't need to go through the text-to-column wizard at all?

One way would be to record a macro that does text to columns with the settings you wish. Then invoke that on an empty cell in the workbook open and workbook activate events.

Ah, that's a good thought. Though I'm restricted from using "on open" settings, due to the way these workbooks will be implemented, I think I can get by with "on activate", if it's not the initial tab. As VBA is definitely NOT my forte, can you give me an example of what language to use on either side of the "active" parts of my macro?

(That is, do I put something like "Private Sub Worksheet_Activate()" somewhere? And, can I take out hte defined range, and have it work on, say, anything pasted in on that particular tab?

PHP Code: 

Sub TxtToCol()
'
' TxtToCol Macro
'

'
    Selection.TextToColumns Destination:=Range("C8"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End Sub 


Try this is the worksheet module:

Please Login or Register  to view this content.

Yup, sorry, try this:

Please Login or Register  to view this content.

I used Walkenbach's VBA for Dummies a few weeks ago for a class and liked it.