likeabottle,

Welcome to the Excel Forum.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.



Option Explicit
Sub CopyA1FromAllSheetsToMaster()
' stanleydgromjr , 02/20/ 2013
' http://www.excelforum.com/excel-programming-vba-macros/901320-how-to-code-a-macro-to-extract-data-from-multiple-sheets-into-one-sheet.html
Dim ws As Worksheet, wM As Worksheet
Dim nr As Long
Application.ScreenUpdating = False
If Not Evaluate("ISREF(Master!A1)") Then Worksheets.Add(Before:=Worksheets(1)).Name = "Master"
Set wM = Worksheets("Master")
wM.Columns(2).Clear
nr = 0
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Master" Then
    nr = nr + 1
    wM.Cells(nr, 2).Value = ws.Cells(1, 1).Value
  End If
Next ws
wM.Columns(1).AutoFit
wM.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the CopyA1FromAllSheetsToMaster macro.