pull same cell from multiple sheets into master column

[CherieLewis]

I have a sales tax workbook that has approximately 100 tabs. Each tab is uniquely named for a different city or county that tax is paid on. I am trying to pull the code assigned by the state for each tab (all listed in cell C1 of each tab) into a master list without having to copy and paste or doing the =cell value for each tab. I have read thru multiple posts and found formulas and macros for workbooks that have generic naming, but I have not seen anything for unique named tabs. Is this something that can be done? Below is a screen shot of where I need the information to populate from each tab. Any help is greatly appreciated.

 

[gordsky]

is the data in every sheet except the master or are there other sheets that you dont want to pull data from? What is the master sheet name

 

[CherieLewis]

The data is in every other sheet. The master sheet name is CO State & RTD Taxes.

 

[mumps]

Do the different city and county names shown in your screen shot correspond to the sheet names? Are the names in the screenshot in the same order as the sheets in your workbook? Your screen shot doesn't show the column letters. In which column are the names and the codes?

 

[Fluff]

If the sheet names are in col B then you can use this in col A & drag down

=INDIRECT("'"&B2&"'!C1")

 

[CherieLewis]

The names on the master are the full city/county names. The sheet names include the city or county but also have the tax code added to the end. The names are in the same order. The names shown above are in column B of the master. I have attached a screenshot of one of the sheets. I am looking to pull number in cell C1 of each sheet into the master column shown above. You can see how the sheets are named in this shot also.

 

[mumps]

This macro assumes that the names in column B of the Master sheet start in row 2, the headers are in row1 and the sheets containing the value in C1 start at sheet3 .

Sub ReturnCellVal()
    Application.ScreenUpdating = False
    Dim x As Long
    For x = 3 To Sheets.Count
        Sheets("Master").Cells(Sheets("Master").Rows.Count, "A").End(xlUp).Offset(1) = Sheets(x).Range("C1")
    Next x
    Application.ScreenUpdating = True
End Sub

 

[CherieLewis]

Thank you mumps. The names in column B on the master actually start on row 34. The headers are on row 33.

 

[mumps]

As long as the sheets containing the value in C1 start at sheet3, try:

Sub ReturnCellVal()
    Application.ScreenUpdating = False
    Dim x As Long, y As Long: y = 34
    For x = 3 To Sheets.Count
        Sheets("Master").Range("A" & y) = Sheets(x).Range("C1")
        y = y + 1
    Next x
    Application.ScreenUpdating = True
End Sub

 

[CherieLewis]

I tried running the macro and got "run time error 9: subscript out of range"