Find Worksheet by name in any open workbook

[Steve McBride]

Excel 2002.

From within a Macro, is it possible to locate and copy an entire spreadsheet
based on the worksheet name only? When I try to record the macro, I can't
get around it specifying the workbook name. Asking the user to specify the
workbook name (to populate a variable) is just asking for trouble.

The data I want to retrieve is dumped from a host system into an Excel file.
The name of the workbook will vary, but the worksheet name will always be
the name of the host system query ("AQSD90_S").

I would like to create a macro that finds the worksheet and copies the data
into it's own worksheet and then proceeds to calculate various data.

Any help would be appreciated.

Thanks,
Steve

 

[J.E. McGimpsey]

One way:

Worksheets("AQSD90_5").Copy

will copy the sheet to a new workbook, and activate that workbook.
So you can use:

Worksheets("AQSD90_5").Copy
With ActiveWorkbook
'do your calculations here
.SaveAs "New Filename"
.Close SaveChanges:=False
End With

 

[Steve McBride]

Thanks, J.E.

I will give that t try right away.

Steve