+ Reply to Thread
Results 1 to 14 of 14

Macro to extract values from all sheets after matching the Sheetname and Item Number

  1. #1
    Forum Contributor
    Join Date
    06-03-2004
    Location
    India
    MS-Off Ver
    2013
    Posts
    205

    Macro to extract values from all sheets after matching the Sheetname and Item Number

    Thanks a lot
    Attached Files Attached Files
    Last edited by prkhan56; 07-15-2016 at 08:33 AM.

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,048

    Re: Macro to extract values from all sheets after matching the Sheetname and Item Number

    So instead of creating a report with a macro, why not just aggregate the data to one DATAtab then use a pivtot table to create the report for you, so you dont have to check EACH sheet if it has a new Item concatenation etc etc.
    That is how I solved issues like this when I am presented them.
    Otherwise you write a ton of code to essentially replace the PivotTable functionality, where it already works great.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Forum Contributor
    Join Date
    06-03-2004
    Location
    India
    MS-Off Ver
    2013
    Posts
    205

    Re: Macro to extract values from all sheets after matching the Sheetname and Item Number

    Hello Mike,
    Thanks for your suggestion but the data is individually entered daily on different sheets every day and it is some work to get all the data under one Data Tab and create a pivot table.

    For this reason I wanted a macro to do it for me. So I can run the code and see the report on any given day.

    Hope to get some help

  4. #4
    Forum Contributor
    Join Date
    06-03-2004
    Location
    India
    MS-Off Ver
    2013
    Posts
    205

    Re: Macro to extract values from all sheets after matching the Sheetname and Item Number

    Hello Everyone,
    Any help on this please

    Thanks

  5. #5
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,048

    Re: Macro to extract values from all sheets after matching the Sheetname and Item Number

    Quote Originally Posted by prkhan56 View Post
    Hello Mike,
    Thanks for your suggestion but the data is individually entered daily on different sheets every day and it is some work to get all the data under one Data Tab and create a pivot table.

    For this reason I wanted a macro to do it for me. So I can run the code and see the report on any given day.

    Hope to get some help
    I would still have a data-tab to aggregate the data instead of a macro to just create the report.

  6. #6
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Macro to extract values from all sheets after matching the Sheetname and Item Number

    I do not think many viewers understood your request.

    A" want a macro which should fill in numbers from Column L on all Sheets after matching the Sheetname and ItemNo."

    You have not shown on a sample where is column L in all sheets.

  7. #7
    Forum Contributor
    Join Date
    06-03-2004
    Location
    India
    MS-Off Ver
    2013
    Posts
    205

    Re: Macro to extract values from all sheets after matching the Sheetname and Item Number

    Hello ABB
    I have attached a new sample
    I have just shown till Sheet7 for explanation

    Thanks for your time

    Rashid
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Macro to extract values from all sheets after matching the Sheetname and Item Number

    The code works on the sample provided. If you change the layout and format of the data, I need to change the code too.
    Attached Files Attached Files
    Last edited by AB33; 07-11-2016 at 03:54 AM.

  9. #9
    Forum Contributor
    Join Date
    06-03-2004
    Location
    India
    MS-Off Ver
    2013
    Posts
    205

    Re: Macro to extract values from all sheets after matching the Sheetname and Item Number

    Hello AB33,
    Thanks for your code but it is not giving the right results as per my OP
    I mentioned in my OP that the Item Nos may or may not be in sorted order
    I have run your macro and I am attaching the results on a sample data where I have changed the Item No on Sheet3 and colored it for explanation sake.

    The code is running only till 3 rows. Please see the Summary sheet after running the code.
    Also see that the Item No and the values are not matching (See Sheet3 colored cells)

    Kindly amend the code accordingly and also would you please exclude Sheet1 and Sheet2 from your code


    Thanks for your time and help
    Rashid
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Macro to extract values from all sheets after matching the Sheetname and Item Number

    Yes, it would not have given the correct result. I was misread your question of ItemNo. meant to be serial number which is column A, not Description.

    "would you please exclude Sheet1 and Sheet2 from your code?"
    Right now, the code includes all sheets, but can easily change once you are happy with the result.


    On your out put sample sheet, item nos on sheet are not on the right order. Try the attached.
    Attached Files Attached Files
    Last edited by AB33; 07-13-2016 at 04:05 PM.

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: Macro to extract values from all sheets after matching the Sheetname and Item Number

    This should do.
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    06-03-2004
    Location
    India
    MS-Off Ver
    2013
    Posts
    205

    Re: Macro to extract values from all sheets after matching the Sheetname and Item Number

    Hello AB33,

    Great code. You are a real Guru.

    Can you fix 2 things for me please?
    1) It should exclude Sheet1 and Sheet2
    2) It should put 0 (zero) on Summary Sheet instead of leaving blanks where the Item Nos are not found on Sheets.

    The code now shows a blank against any Item No which was not found

    Hope to get your help and Thanks a million in advance.

    Rashid

  13. #13
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Macro to extract values from all sheets after matching the Sheetname and Item Number

    Try the attached
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    06-03-2004
    Location
    India
    MS-Off Ver
    2013
    Posts
    205

    Re: Macro to extract values from all sheets after matching the Sheetname and Item Number

    Thanks a lot.
    Code is perfect.
    Thread marked as SOLVED.

    @Jindon - Thanks for your solution too.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Using If statement to link item number and item from two different sheets
    By jaylahaye1974 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-14-2015, 09:43 AM
  2. Replies: 30
    Last Post: 08-17-2015, 01:18 AM
  3. Multiply with cell value for each matching sheetname in a list
    By cajand in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-09-2014, 01:52 PM
  4. [SOLVED] Cell = sheetname get values from sheetname
    By soli004 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-29-2014, 10:00 AM
  5. MS query needs to extract records with matching values
    By knuckleheadmike in forum Excel General
    Replies: 0
    Last Post: 05-16-2014, 10:35 PM
  6. VBA Extract unique values depending on selected item in another column
    By DavidBW in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-08-2013, 05:30 AM
  7. Excel 2007 : Help matching data based on item number
    By SubtleFudge in forum Excel General
    Replies: 6
    Last Post: 04-10-2012, 04:42 PM

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