Excel VBA Text To Columns Problem

[scribbler99]

Can any of you guys help with the following problem.
I receive a spreadsheet in which a number of cells contains lists of numbers seperated by comma's. At present(due to lack of blank cells) I copy the data from their current cells and place it into a different column so that I can manipulate it.

My Problem is that due to me carrying the copying out over multiple worksheets the code I use will not then work to convert data to columns as the woksheets still show cells as being highlighted. I have tried, using code, to deselect cells but I get an error every time. I can get all this to work if I do not copy data to mulitple sheets but wanted to achieve a smarter answer than doing this sheet by sheet.

The error I receive is runtime 1004 method out of range class failed.

Private Sub Workbook_Open()
    
    'RelocateData
    For Shts = 1 To Sheets.Count
        Worksheets.Select (Shts)
        Range("G22:M30").Select
        Selection.Copy
        Range("S22").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
    Next
    Worksheets(2).Select
    'Range("S22").Select
    SendKeys "{ESC}"
    
         
      ' ConvertTextToColumns
        Worksheets.Select (1)
        Range("A100").Value = "1"
        
      
      For Shts = 1 To Sheets.Count
        Range("S21:S30").Select
        
        Selection.TextToColumns Destination:=Range("T21"), DataType:=xlDelimited, _
            TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _
            :=False, Comma:=True, Space:=True, Other:=False, FieldInfo:=Array( _
            Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), _
            Array(8, 1), Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True
      Next
End Sub

 

[SkipVought]

Hi,

This forum is for MS Access, not MS Excel.

Please REPOST your question in forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[scribbler99]

Ok sorry Skipvought I'm pretty new to these forums.