VBA Loop through Sheets

[BelfastHatter]

Hello I have a macro that should loop through the sheets until it reaches the sheet named "HO". However it still tries to run the macro on this sheet.

Please can someone let me know where I have gone wrong.

Here is my code.

Sub Nursery()


Dim ws As Worksheet


For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "HO" Then

Application.DisplayAlerts = False

ActiveSheet.Copy

Dim src As Variant
src = ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)
ActiveWorkbook.BreakLink src(1), xlLinkTypeExcelLinks


Dim path As String
Dim filename As String
path = "T:\Nursery Financial Reports" & Range("B1") & "" & Range("C1") & ""
filename = Range("B1") & " - " & Range("Q1")
ActiveWorkbook.SaveAs filename:=path & filename & ".xls", FileFormat:=xlNormal


Application.DisplayAlerts = True


ActiveWorkbook.Close


ActiveSheet.Next.Select


End If
Next ws


End Sub



Thanks

 

[NdNoviceHlp]

Get rid of is line of code and please use code tags. HTH. Dave

ActiveSheet.Next.Select

ps. U should really take the alert/screen updating stuff outside of the loop. Also why are U copying the sheet in the loop?

 

[MARK858]

Just guessing as you haven't described what you want the code to do but what happens with...

Sub Nursery()


Dim ws As Worksheet


For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "HO" Then

Application.DisplayAlerts = False

ws.Cells.Copy

Dim src As Variant
src = ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)
ActiveWorkbook.BreakLink src(1), xlLinkTypeExcelLinks


Dim path As String
Dim filename As String
path = "T:\Nursery Financial Reports" & ws.Range("B1") & "" & ws.Range("C1") & ""
filename = ws.Range("B1") & " - " & ws.Range("Q1")
ActiveWorkbook.SaveAs filename:=path & filename & ".xls", FileFormat:=xlNormal


Application.DisplayAlerts = True


ActiveWorkbook.Close




End If
Next ws


End Sub

 

[MARK858]

Change the ws.Cells.Copy in the code I posted to ws.Copy.