Macro: Finding and Picking the Tab You Need

Doesn’t it feel good when you come up with an ingenious solution to a problem? A couple of posts ago I mentioned that our data source changed which led to my macro breaking. I’ve been working on some fixes and thought I was very close to finishing when I found out some major changes were implemented.

One of the changes was the addition of more tabs to the data source file: instead of seeing two tabs, representative of two division in our company, I can expect other tabs to be included…but I do not need them in our reports.

When I first started the project, I asked if the file or the two tab’s names ever changed and the response was neither ever changed: the data source always came with two tabs and the tab names were always the same. So I designed the macro to go looking for a particular tab name and pull in its data to use in generating reports. Well, what do you know, when the next time to do the reports rolled around, the file came back with two tabs but with different tab names.

So the next solution was to get around searching for a particular tab name since the tab name could change. I decided to see if there was a unit that always remained consistent. Typically, a unit could be added or removed from the division but there was a corporate unit that seemed to always appear in one of the tabs – the tab I didn’t need. So I set the macro to see if the tab contained that unit, do not use that tab…use the other tab. But this works only if there are no more than two tabs.

This past week the data source came back with 5 or 6 tabs.

Okay, I need to be able to identify the tab I want to use without using tab names but also with a better identification of the desired tab. I finally came up with searching for 3 units and if any one of the three units is found under a particular column, then pull in the data from that tab. (In the file with the macro, I have a tab that has a list of reports to create via macro and the associated units to go within each report.) The particular column is critical because I could come across a data source file that has a tab with those 3 units but the tab is still not the tab I want. Which 3 units do I use? Here’s where I think I was ingenious. After the macro file is opened, the file will randomly select 3 units with the following formula found in the tab with the list of reports to be generated and the associated units to go in the reports:

=INDEX($C1:$C10000,MATCH(RANDBETWEEN(1,COUNTA($C1:$C10000)),$A1:$A10000,0),1)

Where C column is the name of the units and the A column is the order count of the units (1, 2, 3, 4,…). The B column is the name of the report.

The RANDBETWEEN will randomly generate a number between 1 and the maximum number of units, and then by using the INDEX and the randomly generated number, pull in a unit name from column B.

There will be three of these formulas.

Then the macro will go through each tab in the data source file and search for those three unit names, and if any one of the unit is found in a tab, then use the data in that tab. The odds of finding none of those unit names is very small, especially if I’m randomly pulling them from a list. The reason for a random pull rather than the first 3 unit names found in the list of units is because that list is by report order and if a report goes away, which happens, then those units go away too. So if the first report goes away and that report contains more than 3 units to report, then all of those consecutive units will be missing. With all three units missing, the macro won’t pull in any data from the data source file, even though we have data that can be pulled for the other reports.

---

Sample Macro

Dim FindPhrase1 As Variant               'For searches
Dim FindPhrase2 As Variant
Dim FindPhrase3 As Variant
Dim prop1 As String                      'For searches
Dim prop2 As String
Dim prop3 As String
Dim RowLoc As Variant                   'Row location of found text

‘Pulling in the 3 units names
prop1 = Sheets("Predetermined List of Tabs").Range(Cells(4, Z), Cells(4, Z))
prop2 = Sheets("Predetermined List of Tabs").Range(Cells(5, Z), Cells(5, Z))
prop3 = Sheets("Predetermined List of Tabs").Range(Cells(6, Z), Cells(6, Z))

‘Go through each tab in the data source file. Search for the 3 units in each tab.
For Each sheet In wbSource.Sheets
       Sheets(sheet.Name).Activate
       Set FindPhrase1 = Sheets(sheet.Name).Range("A:B").Find(What:=prop1, LookIn:=xlValues)
       Set FindPhrase2 = Sheets(sheet.Name).Range("A:B").Find(What:=prop2, LookIn:=xlValues)
       Set FindPhrase3 = Sheets(sheet.Name).Range("A:B").Find(What:=prop3, LookIn:=xlValues)
           
‘If you find one of those 3 units, then copy the data from that tab (sheet)  
‘endmark is the last row of the tab holding the data in the file with the macro   
      If Not FindPhrase1 Is Nothing Or Not FindPhrase2 Is Nothing Or Not FindPhrase3 Is Nothing Then
          irow = wbSource.Sheets(sheet.Name).Range("A1").SpecialCells(xlLastCell).row                   
          icol = wbSource.Sheets(sheet.Name).Range(Cells(irow, 1), Cells(irow, 1)).End(xlToRight).Column

          Set rangetocopy = wbSource.Sheets(sheet.Name).Range(Cells(1, 1), Cells(irow, icol))
          wbDest.Sheets("Data").Activate
          rangetocopy.Copy wbDest.Sheets("Data").Range(Cells(endmark, 2), Cells(endmark, 2))
         endmark = endmark + irow
         wbSource.Activate
    End If
Next sheet