MACRO WONT WRITE TO WORKSHEETS - HELP

[WBURKERT]

Please help (I have a hunch I am missing just one instruction)
-This routine first deletes all worksheets named SEC1 through SEC200 - this works fine
-then recreates 200 new worksheets named SEC1 through SEC200 - this works fine. I always write new data to all worksheets SEC1 through SEC200 and found that trying to clear 200 worksheets took a long time so I just delete them them and re-add them. - Again this works fine
-then the macro goes through a list looking for records with 1.01 and writes all records associated to 1.01 to Sec1 - this works
-then the macro increments to 2.01 and then looks for all records associated to 2.01 and writes them to Sec2 - THIS DOES NOT WORK. The macro increments to 2.01, searches through my file and finds records associated to 2.01 but will not write the data to SEC2. If I tell the macro that the first section is 2.01 then it writes fine to SEC2 but then increments but won't write to 3.01. It seems that the macro writes to the first section but no others and I sense I might have to release control of Sec1 before moving to Sec2. Please take a look through my code and see if you can help me out. I really don't want to re-write too much so please be kind with my coding. Thanks in advance.

Sub PARTS_To_Sections()
Dim Count As Long
For Count = 1 To 200
Application.DisplayAlerts = False
Sheets("Sec" & Count).Delete
Application.DisplayAlerts = True
Next Count

For Count = 1 To 200
Worksheets.Add().Name = "Sec" & Count
Next Count

Dim iRow As Integer
Dim y As Integer
Dim i As Integer
Dim s As Long
Dim c As String
Dim SecNo As String

For s = 2 To 3
SecNo = s + c

For iRow = 1 To 303 'SKIP first row when Reading and Writing - ALWAYS COLUMN HEADINGS

Application.StatusBar = "SecNo = " & SecNo & " iRow = " & iRow & " iRow, 6 = " & Worksheets("PARTS").Cells(iRow, 6)

If Worksheets("PARTS").Cells(iRow, 6).Text = SecNo And Worksheets("PARTS").Cells(iRow, 9).Text = "Y" _
Then
With Worksheets("Sec" & s)
.Cells(i, 1) = Worksheets("PARTS").Cells(iRow, 2)
.Cells(i, 2) = Worksheets("PARTS").Cells(iRow, 3)
.Cells(i, 3) = Worksheets("PARTS").Cells(iRow, 4)
End With
i = i + 1
End If

If Worksheets("PARTS").Cells(iRow, 7).Text = SecNo And Worksheets("PARTS").Cells(iRow, 9).Text = "Y" _
Then
With Worksheets("Sec" & s)
.Cells(i, 1) = Worksheets("PARTS").Cells(iRow, 2)
.Cells(i, 2) = Worksheets("PARTS").Cells(iRow, 3)
.Cells(i, 3) = Worksheets("PARTS").Cells(iRow, 4)
End With
i = i + 1
End If

If Worksheets("PARTS").Cells(iRow, 8).Text = SecNo And Worksheets("PARTS").Cells(iRow, 8).Text = "Y" _
Then
With Worksheets("Sec" & s)
.Cells(i, 1) = Worksheets("PARTS").Cells(iRow, 2)
.Cells(i, 2) = Worksheets("PARTS").Cells(iRow, 3)
.Cells(i, 3) = Worksheets("PARTS").Cells(iRow, 4)
End With
i = i + 1
End If

Next iRow

Next s

Worksheets("Sec2").Select
Worksheets("Sec2").Range("Print_area").Sort _
Key1:=Worksheets("PARTS - By Section and DESC").Range("D1"), _
Key2:=Worksheets("PARTS - By Section and DESC").Range("A1")


End Sub

 

[PHV]

SecNo = s + c
What is c ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WBURKERT]

Opps, too much cut and pasting going on.

Right after DIM SecNo As String should be

c = 0.01
i = 2
y = 1

Sorry about that.

 

[PHV]

I don't see where y is used in your code.
Anyway, I'd put the i = 2 statement inside the s loop.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WBURKERT]

PHV - You're the man! That fixed it right up - I think my bad code was writing the data but to rows that were off the screen. I only expect a few lines of data each time so when there was nothing on the screen I assumed nothing was written. Thank-you for fixing your code )

I am completely satisfied with the Excel Macros. I have one that merges two files together, then this one that seperates all the data into seperate worksheets. Then I have a Master Word .doc with a macro that pulls data from each worksheet into WORD tables. The final WORD .doc has a nice TOC and INDEX and a couple hundred tables throughout that is saved a .pdf. It's a detailed price list!! Then soon to be an illustrated catalog.

I hope to then figure out how to do it better and faster and all one macro in either WORD or EXCEL. I still have work to do with some different table layouts but I think I have it now.