How to select multiple sheets based on value in a cell 1

[AaronKamp]

I tried searching for a similar topic but did not find anything that answered my question...

I would like some input as to a relatively simple method for selecting multiple sheets within a workbook based on a value located in a common cell (i.e. if E17 has any value other than zero, that sheet is selected).

Background as to WHY I'm doing this for those who may care:
I have inherited an Excel file that is used for invoicing client groups (there are currently 52 sheets that contain data for groups or group divisions). The process was entirely manual and I have automated 95% of the workflow. Due to the fact that multiple people from different company campuses use the file I was unable to entirely scrap the previous process and exclusively use Access. Further, I only had 2 weeks with a 15% time/resource allocation to the project.

When the sheets are printed, the user has to manually select the invoices that have data (invoices are bi-weekly) as for some periods groups may not have any service usage.

 

[Loomah]

Hi
This may (or may not) be what you are after.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
    If Not Intersect(Target, Range("E17")) Is Nothing Then
        Worksheets(Target.Value).Activate
        If Err.Number <> 0 Then
            MsgBox "No such sheet"
            Target.Select
            On Error GoTo 0
        End If
    End If
End Sub

Something like this may have been better off posted in the VBA forum, but hey-ho!!! This is based on entering the index of a specific sheet and may need some modernisation to match your needs.
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Zathras]

Try this:

Sub SelectE17()[green]
' Macro to select all worksheets that have a value in cell E17[/green]
Dim wks As Worksheet
Dim a() As Integer
Dim i As Integer

  For Each wks In Worksheets
    If wks.[e17] <> "" Then
      i = i + 1
      ReDim Preserve a(i)
      a(i - 1) = wks.Index
    End If
  Next
  If i > 0 Then
    ReDim Preserve a(i - 1)
    Sheets(a).Select
  Else
    MsgBox "No sheets with value in E17"
  End If
End Sub

 

[AaronKamp]

Thanks for the quick responses, you guys rock!

The second code set from Zathras worked flawlessly, all I needed to do was tweak line 7 to <> 0 vs <> the empty set.

For whatever reason, I couldn't get Loomah's code to work properly in my project. Improving my VBA skills (or lack of) is definitely on my "To Do" list. I do thank you for your help though, and I will spend some time tonight at home trying to figure it out (that's how I learn best).

 

[Loomah]

Aaron
You can ignore my code. I've just re-read your question and I'd gone off on completely the wrong track! Mine is to select a sheet based on a value in E17 of a specific sheet

That's what you get for posting after midnight!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Zathras]

Aaron: Happy to be of service! Don't neglect your best learning tool, i.e. the Macro Recorder. (Tools menu)

Also, the help system can provide a wealth of information, once you learn its quirks!