Tab name issue

[knightwriter]

Hi,

The macro I am using, finds the excel workbook and pulls the data within a tab name into one data dump sheet but if my workbook has more than one tab, the macro does not pick up the 2nd and 3rd tab name etc. Any ideas how to fix this?

 strBus = Range("c" & iRow).Value

 Select Case strImportType
                        Case "Part1"
                                          
                        Case "Part2", "Part3", 
                            Windows(strSourceWorkbook).Activate
                            Sheets(strSourceWorksheet).Select
                            
                            Range(strRangeMap).Select
                            Selection.Copy
    
                            Windows(strME).Activate
                            Sheets(strDestination).Select
                            
                           Range("A" & iStart & ":A" & iStart + iRows - 1) = strBus
                            Range("B" & iStart & ":B" & iStart + iRows - 1) = strSourcePopulation
                            Range("C" & iStart & ":C" & iStart + iRows - 1) = Now()
                            Range("E" & iStart).Select