+ Reply to Thread
Results 1 to 5 of 5

How to code a macro to extract data from multiple sheets into one sheet?

  1. 02-20-2013, 09:22 PM #1
    likeabottle is offline
    Registered User
    Join Date
    02-20-2013
    Location
    Los Angeles, Ca
    MS-Off Ver
    Excel 2007
    Posts
    2

    Smile How to code a macro to extract data from multiple sheets into one sheet?

    Hello! I am new to this forum and to Macros/Visual Basic. I've been doing my best to research how to write a code, that's probably considered very simple to those on this forum... but i'm failing horribly probably because I don't know enough. Please bare with me as I'm still learning and do not know many of the terminologies needed to clearly describe my question.

    I want to extract data from a cell on every sheet in a workbook, and put in a new sheet like a report. For example, I want it to create a new sheet "master". Copy cell a1 in sheet1, paste it on the master sheet cell b1... then go to sheet2 copy cell a1 and paste it on the master sheet cell b2. I want this to loop until there are no more sheets left in the file.

    I've tried to record a macro, but I don't know how to change the code to select the first sheet and then continue to the next. Same with telling it to go down the rows instead copying it into the same cell.

    As you can see, I have very little knowledge of this stuff and any help would be greatly appreciated!!!

    Thank you!

    btw, I'm heading home for the night, so I won't be able to respond until tomorrow.
    Reply With QuoteRegister To Reply
  2. 02-20-2013, 10:59 PM #2
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: How to code a macro to extract data from multiple sheets into one sheet?

    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.


    Please Login or Register  to view this content.

    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.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
    Reply With QuoteRegister To Reply
  3. 02-20-2013, 11:01 PM #3
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: How to code a macro to extract data from multiple sheets into one sheet?

    likeabottle,

    Hello! I am new to this forum and to Macros/Visual Basic. I've been doing my best to research how to write a code
    See if something below will help you.


    Training / Books / Sites as of 2/17/2013

    MrExcel's Products: Books, CDs, Podcasts Discuss topics related to Holy Macro! Products: Learn Excel from MrExcel, VBA and Macros for Microsoft Excel,Holy Macro! It's 2500 VBA Examples CD, Guerilla Data Analysis Using Microsoft Excel and Excel Knowledge Base CD and the MrExcel Podcasts.
    http://www.mrexcel.com/forum/forumdisplay.php?f=19

    There are over 1800 Excel videos/tutorials here:
    http://www.youtube.com/user/ExcelIsFun

    Getting Started with VBA.
    http://www.datapigtechnologies.com/ExcelMain.htm

    If you are serious about learning VBA try
    http://www.add-ins.com/vbhelp.htm

    Excel Tutorials and Tips - VBA - macros - training
    http://www.mrexcel.com/articles.shtml

    Excel 2007 VBA materials to learn here:
    http://www.worldbestlearningcenter.c...erstanding.htm

    Here's a good primer on the scope of variables.
    http://www.cpearson.com/excel/scope.aspx

    See David McRitchie's site if you just started with VBA
    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    What is a Visual Basic Module?
    http://www.emagenit.com/VBA%20Folder...vba_module.htm

    Here is a good introductory tutorial using a VBA Class:
    http://www.cpearson.com/excel/classes.aspx

    Ron de Bruin's intro to macros:
    http://www.rondebruin.nl/code.htm

    Ron's Excel Tips
    http://www.rondebruin.nl/tips.htm

    Anthony's Excel VBA Page - Excel Application - Excel Consultant - Excel Consulting (see 3 tutorials in Excel VBA Basic Tutorial Series)

    http://www.jlathamsite.com/Teach/VBA...troduction.pdf (95 page "book")

    BET: Microsoft Excel Visual Basic

    Creating An XLA Add-In For Excel, Writing User Defined Functions In VBA
    http://www.cpearson.com/excel/createaddin.aspx

    How do I create a PERSONAL.XLS(B) or Add-in
    http://www.rondebruin.nl/personal.htm

    Creating custom functions
    http://office.microsoft.com/en-us/ex...117011033.aspx

    Writing Your First VBA Function in Excel
    http://www.exceltip.com/st/Writing_Y...Excel/631.html

    VBA for Excel (Macros)
    http://www.excel-vba.com/excel-vba-contents.htm

    Excel Macros Tutorial
    http://www.excel-vba.com/excel-vba-contents.htm

    Excel Macros & Programming
    http://www.excel-vba.com/index.htm

    VBA Lesson 11: VBA Code General Tips and General Vocabulary
    http://www.excel-vba.com/vba-code-2-1-tips.htm

    Excel VBA -- Adding Code to a Workbook
    http://www.contextures.com/xlvba01.html

    Beyond Excel's recorder

    Helpful as a simple concise reference for the basics, and, using the macro recorder is endlessly powerful.
    http://www.techonthenet.com/excel/cells/index.php

    Learn to debug:
    http://www.cpearson.com/excel/debug.htm

    How To: Assign a Macro to a Button or Shape
    http://peltiertech.com/WordPress/how...tton-or-shape/

    User Form Creation
    http://www.contextures.com/xlUserForm01.html

    When To Use a UserForm & What to Use a UserForm For
    http://www.ozgrid.com/Excel/free-tra...ba2lesson2.htm

    Excel Tutorials / Video Tutorials - Functions
    http://www.contextures.com/xlFunctions02.html

    INDEX MATCH - Excel Index Function and Excel Match Function
    http://www.contextures.com/xlFunctions03.html

    Excel Data Validation
    http://www.contextures.com/xlDataVal08.html#Larger
    http://www.contextures.com/excel-dat...ation-add.html

    Excel -- Data Validation -- Create Dependent Lists
    http://www.contextures.com/xlDataVal02.html

    Your Quick Reference to Microsoft Excel Solutions
    http://www.xl-central.com/index.html

    New! Excel Recorded Webinars
    http://www.datapigtechnologies.com/ExcelMain.htm

    Fuzzy Matching - new version plus explanation

    Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
    This page describes how to write code that modifies or reads other VBA code.
    http://www.cpearson.com/Excel/vbe.aspx

    VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad

    Excel Hacks 100 Industrial-Strength Tips & Tools, by David & Traina Hawley

    VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad

    Excel 2007 Book: you can try this...there is a try before you buy ebook available at this link…

    by Stephen/ Bovey, Rob/ Green, John Bullen (Paperback - Feb 11, 2005)
    Professional Excel Development

    by Rob Bovey, Stephen Bullen, John Green, and Robert Rosenberg (Paperback - Sep 26, 2001)
    Excel 2002 VBA: Programmers Reference

    "Professional Excel Development" by Rob Bovey, Dennis Wallentin, Stephen Bullen, & John Green

    DonkeyOte: My Recommended Reading, Volatility
    http://www.decisionmodels.com/calcsecretsi.htm

    Sumproduct
    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    Arrays
    http://www.xtremevbtalk.com/showthread.php?t=296012
    http://www.vbtutor.net/vba/vba_chp21.htm

    Array Dimensions in Visual Basic - Working with Dimensions (code and graphics)
    http://msdn.microsoft.com/en-us/libr...(v=VS.80).aspx

    Shortcut Keys in Excel 2000 through 2007

    Pivot Intro
    http://peltiertech.com/Excel/Pivots/pivotstart.htm
    Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube
    Excel Pivot Table -- Dynamic Data Source
    Overview of PivotTable and PivotChart reports - Excel - Office.com

    Email from XL - VBA
    http://www.rondebruin.nl/sendmail.htm

    Outlook VBA
    http://www.outlookcode.com/article.aspx?ID=40

    Function Dictionary
    http://www.xlfdic.com/

    Function Translations
    http://www.piuha.fi/excel-function-name-translation/

    Dynamic Named Ranges
    http://www.contextures.com/xlNames01.html

    How to create Excel Dashboards
    http://www.mrexcel.com/Excel-dashboards-Xcelsius.html
    http://www.contextures.com/excel-dashboards.html
    http://chandoo.org/wp/excel-dashboards/
    http://chandoo.org/wp/management-dashboards-excel/
    http://www.exceldashboardwidgets.com/
    http://www.andypope.info/charts/gauge.htm

    Excel Dashboard / Scorecard Ebook
    http://www.qimacros.com/excel-dashboard-scorecard.html

    Mike Alexander from Data Pig Technologies
    Excel 2007 Dashboards & Reports For Dummies

    Templates
    http://www.cpearson.com/Excel/Topic.aspx
    http://www.contextures.com/excel-tem...lf-scores.html
    http://www.ozgrid.com/search/templates.htm

    Microsoft Excel Cascading Listboxes Tutorial
    http://www.youtube.com/watch?v=YAMvLJRwZdI

    Date & Time stamping:
    http://www.mcgimpsey.com/excel/timestamp.html

    Get Formula / Formats thru custom functions:
    http://dmcritchie.mvps.org/excel/formula.htm#GetFormat

    A nice informative MS article "Improving Performance in Excel 2007"
    http://msdn.microsoft.com/en-us/library/aa730921.aspx

    Progress Meters
    http://www.andypope.info/vba/pmeter.htm
    http://www.xcelfiles.com/ProgressBar.html

    How to convert text to numbers in Excel
    http://support.microsoft.com/kb/291047

    And, as your skills increase, try answering posts on sites like:
    http://www.mrexcel.com
    http://www.excelforum.com
    http://www.ozgrid.com
    http://www.vbaexpress.com
    http://www.excelfox.com

    If you are willing to spend money for the training, then something here should work for you...
    Amazon.com: excel tutorial dvd
    http://www.amazon.com/s/?ie=UTF8&key...l_5givffg47i_b

    Advanced Excel Training - Online Excel Course
    http://www.udemy.com/advanced-excel/
    Reply With QuoteRegister To Reply
  4. 02-21-2013, 03:38 PM #4
    likeabottle is offline
    Registered User
    Join Date
    02-20-2013
    Location
    Los Angeles, Ca
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: How to code a macro to extract data from multiple sheets into one sheet?

    thank you!!!!
    Reply With QuoteRegister To Reply
  5. 02-21-2013, 04:51 PM #5
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: How to code a macro to extract data from multiple sheets into one sheet?

    likeabottle,

    You are very welcome. Glad I could help.

    Come back anytime.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1