+ Reply to Thread
Results 1 to 6 of 6

Get cell value from every sheet in every book.

  1. #1
    Registered User
    Join Date
    11-28-2012
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    2

    Get cell value from every sheet in every book.

    this one drove me so mad i came looking for help!

    so, i have been trying to write a macro to go to a root location, open every workbook in said directory and return the value of A2 from each sheet.

    to start i just tried getting the values to post within a worksheet, but i cant get this to return a value:

    Please Login or Register  to view this content.
    range? cells? something else??
    =SOLVED=
    should be:
    For i = 1 To Sheets.Count
    Cells(i, 1) = Sheets(i).Cells(1, 2)
    Next i

    still need help making this run for a directory...

    i made a directory list and was going about this the hard way of opening each file by name, but would like to find a universal way. all files in folder are excel files.

    Any help greatly appreciated! I am new to the For loop command and links to GOOD references also appreciated.
    Last edited by nomoreimfull; 11-28-2012 at 10:03 PM. Reason: code tags required,pleasse read the forum rules

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Get cell value from every sheet in every book.

    Here is a macro that will do it:
    Please Login or Register  to view this content.
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Get cell value from every sheet in every book.

    This code will loop through each workbook and each sheet to retrieve information.
    Please Login or Register  to view this content.
    Unzip the folder, setup the code in "Get Data" workbook by ensuring the folder address is correct in the code.
    Then run the code.

    "Get Data" workbook is a macro enabled workbook. The other workbooks are .xlsx workbooks. The code only looks for .xlsx workbooks so will not get information from the workbook you are running the code from.
    Attached Files Attached Files

  4. #4
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Get cell value from every sheet in every book.

    Dave, if range("A2") is empty, your macro will not be able to always match the value with the sheet name and workbook name depending on how many empty cell you encounter. I think a row counter is more appropriate here.
    Regards

  5. #5
    Registered User
    Join Date
    11-28-2012
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Get cell value from every sheet in every book.

    thank you

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Get cell value from every sheet in every book.

    Quote Originally Posted by p24leclerc View Post
    Dave, if range("A2") is empty, your macro will not be able to always match the value with the sheet name and workbook name depending on how many empty cell you encounter. I think a row counter is more appropriate here.
    Regards
    Good point.
    I was under the assumption that there would always be something in A2.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1