find Sheet but sheet name not known

Jagwarman
Jagwarman used Ask the Experts™
on
sounds crazy but.

Can an expert help me out with vba code that will look into the sheets in my workbook and find GLOBAL RATES in cell A1

when it finds that I need to copy the sheet and copy it to a new sheet in a different workbook.

I am using the below code to do copy sheets to different workbooks

Sheets("Sheet Name").Copy After:=Workbooks("Weekly Metrics Template.xlsx").Sheets("Sheet Name")

many thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
fanpages

Commented:
Will there be more than one worksheet with "GLOBAL RATES" in cell [A1]?

If so, do you want every matching worksheet to be copied or, perhaps, just the first located & then the searching stops?

Will "Weekly Metrics Template.xlsx" be open concurrently with the workbook that is executing the code required to search/copy the worksheet?
Jagwarman

Author

Commented:
hi Fan pages,

Will there be more than one worksheet with "GLOBAL RATES" in cell [A1]?

No there will only be one

Will "Weekly Metrics Template.xlsx" be open concurrently with the workbook that is executing the code required to search/copy the worksheet?

Yes it will
Software Team Lead
Commented:
try...

Sub test()
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Cells(1, 1) = "GLOBAL RATES" Then
            ws.Copy After:=Workbooks("Weekly Metrics Template.xlsx").Sheets(Workbooks("Weekly Metrics Template.xlsx").Sheets.Count)
            Exit For
        End If
    Next
End Sub

Open in new window

fanpages

Commented:
Thanks Jagwarman.

Ryan has already progressed making those assumptions, so I do not think I need to provide a suggestion.

PS. Just an afterthought; will any worksheets be Hidden (/VeryHidden)?  If so, should they be ignored?
Jagwarman

Author

Commented:
That's perfect Thank you both

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial