Looping Through Sheets in a workbook in Excel 2010

Hi All,

Im new to this so please bear with me. I dont have a lot of experiance in VBA.
Currently i am looking to modify over 1200 workbooks. all with the same name in different folders. inside the workbooks i have different sheets. possibly up to 30 sheets. some of the workbooks would have the same sheets with the same name. Ie Job sheet, Tool List etc. how ever not all these sheets are inside everyone of the workbooks. is there a code that I could use to call the sheet and if that sheet is missing it would skip over the that sepific sheet and find the next piece of code with out erroring out.

Many thanks for your help.

John

John, you can do something like the following:

Please Login or Register  to view this content.

Basically, the above sets wS to the worksheet if it exists. Otherwise it remains set to "nothing". The if test uses a "Not" statement to test for the opposite..

Lots of things to consider. You need to create your list of workbooks before you modify any. Otherwise, if you are querying for them, you will not get them all, as opening a workbook changes the file list order. Use an array to list the sheets that way you can loop through all the sheets. Use subroutines. Don't put hundreds of lines of code between the if..end if above

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

Hi Patel45 and Bob, may thanks for yor speedy replies. I have not had a chance to get back on this VBA project today but i will be back on to it tomorrow. I will reply with feedback tomrw. many thanks again

John

Hi Guys, I have tried doing the code as suggested. I have run with your Bob, however when I run the macro and it comes across a sheet that doesnt excist it give me a "subscript out of range" I persume this is because the sheet is not in the workbook i am going through?
Here is a look at some of the code i am running.

40

Set wS = Nothing
On Error Resume Next
Set wS = Sheets("Tracking Label")
On Error GoTo 50
If Not wS Is Nothing Then
'code to run if sheet exists
Sheets("Tracking Label").Select
'ActiveSheet.Unprotect Password:="Floor#01"
With ActiveSheet.PageSetup
'.LeftFooter = "QD 7.5.1-001-08"
'.RightFooter = "Rev A"
Range("C3:E3,C4:E4,G3").Select
Range("G3").Activate
Selection.ClearContents
'Selection.UnMerge
Range("C3:E3").Select
Selection.NumberFormat = "General"
ActiveCell.Value = "='Cover Sheet'!D3" 'PN
Range("C4:E4").Select
Selection.NumberFormat = "General"
ActiveCell.Value = "='Cover Sheet'!S1" 'Job#
'Range("C5:E5").Select
'Selection.NumberFormat = "General"
'ActiveCell.Value = "='Cover Sheet'!O3" 'Descrip
Range("G3").Select
Selection.NumberFormat = "General"
ActiveCell.Value = "='Cover Sheet'!L1" 'Rev#
'Range("B3").Select
'Rows("5:100").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("A1").Select
'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="Floor#01"

End With
On Error GoTo 50
End If
50


'Then i have code going from 50 on wards. Many thanks for your help.

John

Hi Guys,

Just to let you know i got this eventually to work however your code was perfect and worked really well but for some reason it would bom out on certain workbooks and would throw up an error looking for the sheet to select.

Many thanks for you help on this one.

John