find sheet

[stan]

I have a small marco that loads data from a file from another application
into a workbook in Excel 2003. Sometimes the number of rows exceeds the
65,000+ cap so the remaining data spills over to a second sheet, sometimes
not.

I need a line or two that looks for sheet2: if it finds id it copies all the
data from sheets 1 & 2. If there is no sheet 2, the procedure continues and
it loads sheet 1.

Nothing I try seems to work. I am a beginner with VBA.

 

[Project Mangler]

Stan,

Have a look here:
http://www.cpearson.com/excel/ImportBigFiles.aspx

DB

 

[Barb Reinhardt]

Dim myWS as excel.worksheet

on error resume next
Set myWS = ActiveBook.Worksheets("Sheet2")
on error goto 0

if myws is nothing then
MSgbox("There is no Sheet2")
end if


'Alternatively, you can try this if there are only two worksheets in the
workbook.

if ActiveBook.WOrksheets.count = 2 then
Msgbox("There are two worksheets")
elseif activebook.worksheets.count = 1 then
Msgbox("There is one worksheet")

end if

HTH,

Barb Reinhardt

 

[stan]

Works prefectly. Thank you.
--
stan

Dim myWS as excel.worksheet

on error resume next
Set myWS = ActiveBook.Worksheets("Sheet2")
on error goto 0

if myws is nothing then
MSgbox("There is no Sheet2")
end if


'Alternatively, you can try this if there are only two worksheets in the
workbook.

if ActiveBook.WOrksheets.count = 2 then
Msgbox("There are two worksheets")
elseif activebook.worksheets.count = 1 then
Msgbox("There is one worksheet")

end if

HTH,

Barb Reinhardt