Now i get Compile error "End With end without"

"Simon Lloyd" wrote:


Hi Aussiegirlone, your problem was you didn't close off your with
statements properly:

Try this:

Code:
--------------------
Sub NewRow()

Dim EndRowA As Long
Dim NextRowAF As Long
Dim wks As Worksheet
Dim iRow As Long
Dim i As Long

For i = 6 To Sheets.Count
With Sheets(i)
EndRowA = Cells(.Rows.Count, "A").End(xlUp).Row
NextRowAF = .Cells(.Rows.Count, "AF").End(xlUp).Row + 1
.Cells(NextRowAF, "AC").Value = "Total"
.Cells(NextRowAF, "AF").Formula _
= "=sum(AF5:AF" & NextRowAF - 1 & ")"
With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AC"))
With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AD"))
With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AE"))
.Font.Bold = True
.Font.ColorIndex = 2
.Interior.ColorIndex = 32
.Borders.LineStyle = xlContinuous
.Borders.ColorIndex = 2
.Borders.Weight = xlThin
End With
End With
End With

For iRow = NextRowAF + 1 To 32
If Application.CountA(.Rows(iRow)) = 0 Then
.Rows(iRow).Interior.ColorIndex = 2
End If
Next iRow

.Rows("5:32").RowHeight = 12.75
End With
Next i
End Sub
--------------------
In future when posting code please either click the # at the top of your
new post window and paste the code between the tags or highlight your
code and click the #


Aussiegirlone;418773 Wrote:
::Q1) An error message comes up when I run the code below, ::
::Error message: €œcompile error, next without for€� the highlight area
is (Next wks) right at the bottom of the code in-between (END WITH & END
SUB) can someone fix it::
::Q2) Instead of the code below running on every sheet can someone make
it skip the first 5 sheets using a sheet.count formula please?::


Code:
--------------------

Option Explicit
Sub NewRow()

Dim EndRowA As Long
Dim NextRowAF As Long
Dim wks As Worksheet
Dim iRow As Long

For Each wks In ActiveWorkbook.Worksheets
With wks
EndRowA = Cells(.Rows.Count, "A").End(xlUp).Row
NextRowAF = .Cells(.Rows.Count, "AF").End(xlUp).Row + 1
.Cells(NextRowAF, "AC").Value = "Total"
.Cells(NextRowAF, "AF").Formula _
= "=sum(AF5:AF" & NextRowAF - 1 & ")"
With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AC"))
With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AD"))
With Union(.Cells(NextRowAF, "AF"), .Cells(NextRowAF, "AE"))
.Font.Bold = True
.Font.ColorIndex = 2
.Interior.ColorIndex = 32
.Borders.LineStyle = xlContinuous
.Borders.ColorIndex = 2
.Borders.Weight = xlThin
End With

For iRow = NextRowAF + 1 To 32
If Application.CountA(.Rows(iRow)) = 0 Then
.Rows(iRow).Interior.ColorIndex = 2
End If
Next iRow

.Rows("5:32").RowHeight = 12.75
End With
Next wks
End Sub

--------------------



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116530