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()   

[b]'RelocateData [/b]
   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    

[b]'Trying to select anything here just to move the focus from the selected cells [/b]

Worksheets(2).Select    
SendKeys "{ESC}"                   

[b]' ConvertTextToColumns [/b]
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 ADDs a sheet for the purpose of consolidating the data from the other sheets...

Private Sub Workbook_Open()
    Dim wsSummary As Worksheet, ws As Worksheet, lRow As Long
    
    Set wsSummary = Worksheets.Add
'RelocateData
    For Each ws In Worksheets
        If ws.Name <> wsSummary.Name Then
            ws.Range("G22:M30").Copy
            Range("S22").Select
            With wsSummary
                lRow = .[A1].CurrentRegion.Rows.Count + 1
                .Cells(lRow, "A").PasteSpecial xlPasteValues
            End With
        End If
   
        With wsSummary
            .Range(.[A1], .[A1].End(xlDown)).TextToColumns _
                Destination:=.Range("A1"), _
                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

        End With
   Next
End Sub

Skip,

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

 

[Gavona]

You don't need to select anything - just explicitly refer to the range, including which sheet it is on.

'RelocateData
For Shts = 1 To Sheets.Count
    Worksheets(Shts).Range("G22:M30").Copy
    Range("S22").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Next

Your problem:
Look up "Selecting and Activating Cells" in excel help. That includes "If you use the Select method to select cells, be aware that Select works only on the active worksheet."
So your line: Worksheets.Select (1) activates that sheet and means all the following selections will operate on that Worksheet. (The for statment does not activate or select anything). Qualify the Range eg by Sheets(shts).Range("s...

Incidentally you use a mixture of Sheets and Worksheets in your code. The former includes Charts etc. Suggest you stick with Worksheets throughout.

Hope that helps you find a solution. Post back with revised code and someone here will help further.




Gavin

 

[SkipVought]

this line of code I forgot to remove...

            Range("S22").Select

Skip,

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

 

[scribbler99]

Hi Gavin & Skip

Thanks for taking the time to reply but I still don't get the solution. When I test Gavin's code nothing happens. When I test Skip's code yes I get a new worksheet added but nothing else apart from error's happens. Am I misunderstanding what either of you are telling me ?

 

[SkipVought]

What errors on what statement?

Skip,

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

 

[SkipVought]

The text to columns belongs after the loop.

But a question. You are copying 7 columns. Which column are you parsing? It seems as it you want to parse the FIRST column. But parsing will OVERWRITE the existing columns you copied (H:M) or (T:Y) in your code.

If this is the case, your initial copy ought to be S22:S30

Private Sub test()
    Dim wsSummary As Worksheet, ws As Worksheet, lRow As Long
    
    Set wsSummary = Worksheets.Add
'RelocateData
    For Each ws In Worksheets
        If ws.Name <> wsSummary.Name Then
            ws.Range("G22:M30").Copy
            With wsSummary
                lRow = .[A1].CurrentRegion.Rows.Count + 1
                .Cells(lRow, "A").PasteSpecial xlPasteValues
            End With
        End If
   
   Next
    With wsSummary
        .Range(.[A1], .[A1].End(xlDown)).TextToColumns _
            Destination:=.Range("A1"), _
            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

    End With
End Sub

Skip,

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

 

[scribbler99]

Skip, thanks again for replying. In cell G22 (this is a merged cell) I have a quantity of CSV's which refer to employee's and I wanted to extract these against other values using vlookup to calculate costs (time * pay rate).
As the adjacent cells which they are stored have data I thought my only way was to use the function "text to columns" and extract them elsewhere on the spreadsheet, then do a vlookup. Using my rather limited amateurish coding I was extractiig the results to the next free cells in teh row which are from G22 onwards.

Hope this makes sense.

I have, hopefully, uploaded a copy of the spreadsheet which may make more sense.

 

[SkipVought]

So is there yet a problem?

If so, please explain in detail.

'I have, hopefully, uploaded a copy of the spreadsheet'

Not in your C:\drive! Uploaded???

Skip,

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

 

[scribbler99]

Hi Skip

Firstly sorry for the delay in replying but I have, for almost 2 days, been receiving page error messages on this forum when I tried to submit a reply. Hopefully this gets through.

As you have no doubt guessed I am only a beginner with VBA and this forum but thought it was as simple as just entering my file location in the attachment box to be able to upload it.

I don't know why I was receiving errors on the spreadsheets but I removed any code that I had placed in and started again by doing a copy/paste with your code and hey presto all is fine now. No doubt your not surprised at that.

So now I have cells containing individual employee references. Can you suggest which direction I should go to as now I need to cross reference these to values on another sheet and multiply it by time spend on job. I have in the last day used vlookup but as my knowledge is very minimal I had to write code which semed long winded and I'm sure with experience I could eventually improve. It's virtually a timesheet scenario. I have wondered if I had wasted time by extracting the csv data when I may possibly be better reading it and dealing with it in memory instead.

Regards

Colin

 

[SkipVought]

Please post a sample of the data/table structure of your SOURCE data and how this relates to the DESTINATION sheet.

Skip,

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

 

[scribbler99]

Hi Skip

Not sure what you want to see as I am using Excel. Would it be easier if I attached a screen shot or the spreadsheet. If so can you tell me how to attachment a file ?

Colin

 

[SkipVought]

Don't make it too complex, for instance...
[tt]
CC MACH_GRP TRAVELER OPER TRV_QTY LPST
5B1 11443 2394821 270 1 1/22/2010
5B1 11445 R170327 390 14 2/22/2010
5B1 00010 R619022 100 1 1/3/2010
5B1 00010 R619031 100 1 1/11/2010
[/tt]
Use TGML TT tags (Search in this page for TGML, find the TT tag and use) Preview your post. DELETE the TABS between the columns and replace with necessary SPACES.

Skip,

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

 

[scribbler99]

Sheets 1 to 9 contain following (as well as other info) but there could possibly be more sheets added in future.

[tt][blue]Job No. Start Fin Time Staff_Ref[/blue]
1234 08:00 08:45 00:45 9355,9356,9357,9988
1237 09:00 09:30 00:30 9355,9356,9357,9984
1241 09:30 10:15 00:45 9355,9356,9357,7983
1255 10:30 11:45 01:15 9355,9356,9357,6967

Sheet 10 contains Staff_Ref to cross reference to
[blue]Staff_Ref Name Basic O/T[/blue]
9355 Mr1 6.25 9.00
9356 Mr2 6.35 9.50
6967 Mrs3 7.00 9.00
[/tt]

 

[SkipVought]

Very nice! Clear. Understandable. Well done!

When you parse the Staff_Ref values, you must include a clumn for Job No.
[tt]
Job No. Staff_Ref
[/tt]
Here's a way to do that pretty quick.
[tt]
Copy the Job No. column, heading & data to a new sheet A1.

CUT the Staff_Ref column and insert next to Job No. on the new sheet.

Parse the Staf_Ref data on COMMA, destination format TEXT.

Follow the steps in faq68-5287 to normalize this data.
[/tt]
Post back, reporting your results.




Skip,

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