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.
Bookmarks