Error with For Next
 

Hi

I have atempted to write the code below which should check
Range I3 to I1000 and if there is no "Y" in the cell to
copy the sheet (name held in the offset(0,-2)) to a wb
which is determined by the Case Select Statement.

The code is being stopped with the error message "Compile
Error - Next without For"

Is the to do with me embedding either the IF or CASE
SELECT stament inside the For Next loop?

TIA


Sub Copy_Sheets()
Dim shName As Range
Dim prName As Range
Dim CValue As Range

rng = Columns("I3:I1000")

With Worksheets("Index")
For Each cell In rng

If rng = "Y" Then
shName = cell.Offset(0, -2).Value
prName = cell.Offset(0, -1).Value
CValue = cell.Offset(0, 1)

Select Case prName
Case "Austravel"
prloc = "S:\Kingston\FA\Overseas Payments\Overseas
Payments Public\Remittance\Long Haul\Austravel\Austravel
Crystal Export File.xls"
Case "Tropical"
prloc = "S:\Kingston\FA\Ove0rseas Payments\Overseas
Payments Public\Remittance\Long Haul\Tropical\Tropical
Crystal Export File.xls"
Case "Jetsave"
prloc = "S:\Kingston\FA\Overseas Payments\Overseas
Payments Public\Remittance\Long Haul\Jetsave\Jetsave
Crystal Export File.xls"
End Select

Sheets(shName).Copy After:=Workbooks(prloc).Worksheet.Count
cell = "Y"

Next cell
Else
Next cell
End With

End Sub

Error with For Next
 

Edgar,

Haven't checked the logic, but your If doesn't end.

Replace this

Next cell
Else
Next cell

with

Next Cell
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Edgar" wrote in message
...
Hi

I have atempted to write the code below which should check
Range I3 to I1000 and if there is no "Y" in the cell to
copy the sheet (name held in the offset(0,-2)) to a wb
which is determined by the Case Select Statement.

The code is being stopped with the error message "Compile
Error - Next without For"

Is the to do with me embedding either the IF or CASE
SELECT stament inside the For Next loop?

TIA


Sub Copy_Sheets()
Dim shName As Range
Dim prName As Range
Dim CValue As Range

rng = Columns("I3:I1000")

With Worksheets("Index")
For Each cell In rng

If rng = "Y" Then
shName = cell.Offset(0, -2).Value
prName = cell.Offset(0, -1).Value
CValue = cell.Offset(0, 1)

Select Case prName
Case "Austravel"
prloc = "S:\Kingston\FA\Overseas Payments\Overseas
Payments Public\Remittance\Long Haul\Austravel\Austravel
Crystal Export File.xls"
Case "Tropical"
prloc = "S:\Kingston\FA\Ove0rseas Payments\Overseas
Payments Public\Remittance\Long Haul\Tropical\Tropical
Crystal Export File.xls"
Case "Jetsave"
prloc = "S:\Kingston\FA\Overseas Payments\Overseas
Payments Public\Remittance\Long Haul\Jetsave\Jetsave
Crystal Export File.xls"
End Select

Sheets(shName).Copy After:=Workbooks(prloc).Worksheet.Count
cell = "Y"

Next cell
Else
Next cell
End With

End Sub