Need help writing an If statment

[Justin Young]

Hi all.

I am writing a macro to go away and get data from other
spread sheets, there will be up to 20 spreadsheets to get
information from at one time but sometimes there will only
be 10. I need to tell the macro to skip and go to the next
one if the spreadsheet c does not exist.


Any help would be appreciated.

Thanks.
Justin.

 

[Bob Phillips]

Justin,

You don't need to do it that way, jus iterate through the worksheets
collection. For example,

For Each sh In ActiveWorkbook.Worksheets
'do your thing
Next sh

If you want to not process one, just use code like


For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Summary Sheet" Then
'do your thing
End If
Next sh

--

HTH

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

 

[Tom Ogilvy]

Assuming spreadsheets equates to workbooks:

Dim booklist as Variant, Wkbk as Workbook
Dim i as Long
booklist = Array("Book1.xls","Book2.xls","Book3.xls")
for i = lbound(booklist) to ubound(booklist)
set wkbk = nothing
On Error Resume Next
set wkbk = Workbooks.Open(filename:= _
"C:\Myfolder\" & booklist(i)
On Error goto 0
if not wkbk is nothing then
' process the workbook
wkbk.Close Savechanges:=False
end if
Next

 

[Guest]

Thank you so much for your help.

-----Original Message-----
Assuming spreadsheets equates to workbooks:

Dim booklist as Variant, Wkbk as Workbook
Dim i as Long
booklist = Array("Book1.xls","Book2.xls","Book3.xls")
for i = lbound(booklist) to ubound(booklist)
set wkbk = nothing
On Error Resume Next
set wkbk = Workbooks.Open(filename:= _
"C:\Myfolder\" & booklist(i)
On Error goto 0
if not wkbk is nothing then
' process the workbook
wkbk.Close Savechanges:=False
end if
Next

--
Regards,
Tom Ogilvy





.