find Sheet but sheet name not known

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
JagwarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
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?
JagwarmanAuthor 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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
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?
JagwarmanAuthor Commented:
That's perfect Thank you both
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.