Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

Select worksheet from data in Cell A1 not from Sheet Name

Is it possible to select a worksheet from text that is in Cell A1?

Reason: Sheet names are often different but Cell A1 will always be consistant, So, if the text I am looking for is Document it could be on any of the worksheets I need to select A1 on the Worksheet with Document in A1 when my Macro opens the Workbook,

Possible???????

Thanks
0
Jagwarman
Asked:
Jagwarman
  • 6
  • 6
1 Solution
 
Rgonzo1971Commented:
Hi,

You could try (but beware of duplicates) in your worbook module

Private Sub Workbook_Open()
    For Each sh In Me.Worksheets
       If sh.Range("A1") = "Document" Then
           sh.Range("A1").Activate
       End If
    Next
End Sub

Open in new window

Regards
0
 
JagwarmanAuthor Commented:
Hi Rgonzo1971

thanks for getting back so quickly. Unfortunately it does not seem to work.

I open the workbook which open on Sheet1, the macro then runs and ends but stays on sheet1 whereas it should end up on Sheet 4
0
 
Rgonzo1971Commented:
Hi,

Sorry let's try

Private Sub Workbook_Open()
    For Each sh In Me.Worksheets
       If sh.Range("A1") = "Document" Then
            sh.Activate
            Range("A1").Activate
       End If
    Next
End Sub

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
JagwarmanAuthor Commented:
Unfortunately that is not working either. I have also gone to the sheet that I want to start at and placed the curser in a random cell expecting the macro to go to A1.... but nope. Bizzare
0
 
Rgonzo1971Commented:
Hi,

pls try example

TestOpen.xlsm

Regards
0
 
JagwarmanAuthor Commented:
I presume your file works because the macro is in that file whereas my macro is in a different workbook.

In my workbook I need to open a file that was saved down by someone else [which is a new file every day] and when my workbook opens the file that is when I need it to find the Sheet with the text Document,

I guess this is more complicated as I am not sure I can call a private sub from within my Macro. Or can I?
0
 
Rgonzo1971Commented:
Hi,

pls try this in the ThisWorkbook Module of the Excel file you use to open the file

Public WithEvents App As Application

Private Sub Workbook_Open()
    Set App = Application
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

    For Each sh In Wb.Worksheets
       If sh.Range("A1") = "Document" Then
            sh.Activate
            Range("A1").Activate
       End If
    Next
End Sub

Open in new window

Regards
0
 
JagwarmanAuthor Commented:
Rgonzo1971.

Still not working,

Think we are going to have to give up on this one I have taken up too much of your time already.

Regards
0
 
Rgonzo1971Commented:
I forgot to say that you have to close both files and reopen it to function

regards
0
 
JagwarmanAuthor Commented:
just a thought, when I open the file my macro is not then going to your code. What makes my Macro call your code?
0
 
Rgonzo1971Commented:
My code initilize application events when you open it then every time you open a workbook the

App_WorkbookOpen code will be fired

Regards
0
 
JagwarmanAuthor Commented:
Rgonzo1971

I cannot get this to do what I want it to do, but, it is a macro I will be able to use under different curcumstances so I am going to accept
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now