Error sorting data compile error

bergsh

New Member
Joined
Sep 12, 2006
Messages
13
Here is my code, I am getting an compile error Next witout For. I do not have all of the worksheet names in the workbook. Is there a way to skip over them and proceed to the next worksheet name? Thanks for the help in advance.


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Disperse_Data()
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">' Disperse_Data Macro</SPAN>
<SPAN style="color:#007F00">' Macro recorded 9/14/2006 by Goodyear Tire and Rubber Co</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet

<SPAN style="color:#007F00">'</SPAN>
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\Shawn Berg\My Documents\AC Hose Dimensional Data.xls"
   <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
     <SPAN style="color:#00007F">If</SPAN> ws.Name = "     4828-010O" <SPAN style="color:#00007F">Then</SPAN>
    Range("B1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A1").Select
    ActiveSheet.Paste
    ws.Name = "     428-010I"
        Range("C1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("C1").Select
        ActiveSheet.Paste
    ws.Name = "     428-010V"
        Range("C1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("D1").Select
        ActiveSheet.Paste
     <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
   <SPAN style="color:#00007F">Next</SPAN> ws
    <SPAN style="color:#00007F">If</SPAN> ws.Name = "     4826-010O" <SPAN style="color:#00007F">Then</SPAN>
    Range("B1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A1").Select
    ActiveSheet.Paste
    ws.Name = "     426-010I"
        Range("C1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("C1").Select
        ActiveSheet.Paste
    ws.Name = "     426-010V"
        Range("C1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("D1").Select
        ActiveSheet.Paste
     <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
   <SPAN style="color:#00007F">Next</SPAN> ws
    <SPAN style="color:#00007F">If</SPAN> ws.Name = "     4826-013O" <SPAN style="color:#00007F">Then</SPAN>
    Range("B1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A1").Select
    ActiveSheet.Paste
    ws.Name = "     426-013I"
        Range("C1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("C1").Select
        ActiveSheet.Paste
    ws.Name = "     426-013V"
        Range("C1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("D1").Select
        ActiveSheet.Paste
     <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

How about changing it to

Code:
Sub Disperse_Data()
'
' Disperse_Data Macro
' Macro recorded 9/14/2006 by Goodyear Tire and Rubber Co
Dim ws As Worksheet

'
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\Shawn Berg\My Documents\AC Hose Dimensional Data.xls"
   For Each ws In ActiveWorkbook.Worksheets
     If ws.Name = "     4828-010O" Then
    Range("B1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A1").Select
    ActiveSheet.Paste
    ws.Name = "     428-010I"
        Range("C1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("C1").Select
        ActiveSheet.Paste
    ws.Name = "     428-010V"
        Range("C1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("D1").Select
        ActiveSheet.Paste
     End If
   'Next ws
    If ws.Name = "     4826-010O" Then
    Range("B1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A1").Select
    ActiveSheet.Paste
    ws.Name = "     426-010I"
        Range("C1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("C1").Select
        ActiveSheet.Paste
    ws.Name = "     426-010V"
        Range("C1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("D1").Select
        ActiveSheet.Paste
     End If
   'Next ws
    If ws.Name = "     4826-013O" Then
    Range("B1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A1").Select
    ActiveSheet.Paste
    ws.Name = "     426-013I"
        Range("C1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("C1").Select
        ActiveSheet.Paste
    ws.Name = "     426-013V"
        Range("C1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("D1").Select
        ActiveSheet.Paste
     End If
    Next ws
End Sub

Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,113,955
Messages
5,545,167
Members
410,667
Latest member
Gaexel
Top