Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a macro which calls the "text to column" function
    I want to run the macro on all worksheets of open workbook --at the moment I have to activate each worksheet to enable the macro to work---
    Can "text to column" function be coded so as to work without activating each worksheet??
    thanx
    Smbs

  2. #2
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Could you attatch your existing code, so that we can try modifying that instead of reinventing the wheel?

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts
    snippet attached coded so it works by activating each sheet
    Attached Files Attached Files

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    try this version:

    Code:
    Sub loopSheets()
    Dim i As Integer
    For i = 1 To Worksheets.Count
    
    
    Worksheets(i).Range("A1:A65000").TextToColumns Destination:=Worksheets(i).Range("A1"), DataType:=xlDelimited, _
    		TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    		Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    		:=Array(1, 2), TrailingMinusNumbers:=True
    Next i
    
    End Sub

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Mike has answered whilst I was stabbing at it. I think my approach was a little longer winded, so I am glad he saved me the pain.

  6. #6
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many thanx I get the idea
    Smbs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •