Consulting

Results 1 to 12 of 12

Thread: VBA to add numbers in columns from various workbooks

  1. #1

    VBA to add numbers in columns from various workbooks

    Mark from South Africa. Trust you and the family are keeping safe during the COVID-19 situation.

    I have 1 master workbook, namely: “Example_FC 2020_Master” with 1 tab called “Master” and then I have 2 excl workbooks, namely:
    “Example_FC 2020_Retail_Mark” and “Example_FC 2020_Retail_Peter”, each with a master tab and then various customer name tabs.

    The worksheets consist of products.



    The idea is to forecast per customer, per product. In the master tab of “Example_FC 2020_Retail_Mark” and “Example_FC 2020_Retail_Peter”, I have used vlookup (very basic), to add all the columns from the various tabs in the same workbook, per product to give a grant total per month (all customers forecast quantities added together per product).
    I am sure it is earier to use VBA, than vlookup, but thats for another day.

    What I need is the following.....
    I would like to have a button in the “Example_FC 2020_Master” workbook, that I can press and what it will do is:
    Look (in a specific folder say on my desktop for example), at the 2 excl workbooks namely: “Example_FC 2020_Retail_Mark” and “Example_FC 2020_Retail_Peter”, in their master tabs and extract the quantities, per product and add them together in the “Example_2020_Master” workbook.

    I will eventually have about 10 different workbooks with about 300 products, all exactly the same as “Example_FC 2020_Retail_Mark” and “Example_FC 2020_Retail_Peter” all consolidating to the “Example_FC 2020_Master”

    Can someone help me with the code if possible? Example attached.
    Can someone help me with the code if possible? Example attached - Willing to donate to a charity for assistance.

    "I have posted on 2 other forums"
    Attached Files Attached Files
    Last edited by NewbieMark76; 04-24-2020 at 02:37 AM. Reason: Add coments

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    It seems you have a fairly recent version of Excel. This sort of thing would be perfect for Power Query, are you against such a solution? If not, which version of Excel are you using?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Good Day p45cal. Thank you for your reply. I wouldn't be against Power Query. The problem is, the company I work for won't buy it for me. We use Office 365.

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by NewbieMark76 View Post
    Good Day p45cal. Thank you for your reply. I wouldn't be against Power Query. The problem is, the company I work for won't buy it for me. We use Office 365.
    Then you've very probably got it since it's built in. You should have a Get & Transform data section on the Data tab of the ribbon?

    Could you provide links to all your cross posts? - it's netiquette, and a requirement here (and most other forums).
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by p45cal View Post
    Then you've very probably got it since it's built in. You should have a Get & Transform data section on the Data tab of the ribbon?
    Do you have it?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    I cant find it. I checked add-in, ribbons, etc. No power query listed anywhere

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    I don't think it is called Power Query in office 365, it's Get and Transform Data on the Data tab. I'm 95% sure it's included in Office 365 (perhaps it needs to be enabled or that part of the ribbon made visible - do some internet searching).

    https://support.microsoft.com/en-us/...rs=en-us&ad=us
    2020-04-24_124156.jpg
    It's a lot less work than writing vba code (macros).
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Try over here

    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    Thank you p45Cal and Paul. Found it. Your assistance is much appreciated. Now to learn power query :-)

    Have a great weekend!

  11. #11
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    In the attached is not a solution but a taster, this is because I'm still learning Power Query.
    The pivot table at cell J17 of the attached is the result of Power Query. It's gratifying to see that the results exactly match your desired results.
    You won't be able to update (refresh) the pivot table because my source files are not in the same place as they are on your computer/system but I'll show you how to remedy that:
    Navigate to where you can get the Queries & Connections pane showing at the right of your Excel window:
    2020-04-25_132538.jpg

    Then double click on the vbaExpress67219 query, and in the window which pops up, click on the little gear wheel to the right of the Source step at the top of the Applied Steps list in the Query Settings panel (arrowed 1 in the pic) to get this:
    2020-04-24_224841.jpg
    Click on the Browse… button and navigate to the folder where you've put your files, and click OK.
    Then you need to click on Close and Load at the extreme left of the Home tab of the ribbon to close that window. At the moment it's probably safer to have those files by themselves in a folder; I have put something in the query to restrict the files it looks at, but it's rudimentary.

    Do this first with the source files you attached to your posts here to make sure it refreshes properly.
    Once you've got no errors showing, try changing some data in those files (it looks at the individual customer sheets in those files, and deliberately ignores the master sheets in them)
    Currently it's very rigid in what it expects to see in those files (it won't like anything but two columns and expects to see April 2020 and May 2020 as headers). However, you should be able to add rows to that data, with new Materials and Material Descriptions with numbers and they should appear in the refreshed pivot table, but be aware that the query acts on the files in the folder so you need to save the files once you've made alterations before refreshing the pivot (I don't think you need to close the files).

    I repeat it's a taster and it has its faults: the dates it produces are based on the headers of the first file/sheet it comes across and assumes the rest are the same! The header for the first column is wrong, again it's (currently 'Mark') from the first file name that it looks at. I'll put these right later - just trying to get robust solutions before I do.

    Incidentally, in the Power Query editor, when you first opened it, you should have been able to see the table that's produced by the query, which forms the basis on which the pivot table is made - you won't see that table anywhere else - it's only held in memory (you can change that).

    I also attach, for interest, the table that's produced in the background by the query in the Table.xlsx file. You'll see column A contains the name gleaned from the file name, column D contains the sheet name. It wouldn't be difficult to extract Retail/Trade from the file name too.

    This is an interesting exercise for me and helps me learn, so will continue to try and improve it and make it more robust and flexible - but I'm hardly the person to give you the best advice, so, and I hope I'm not breaking forum rules here, I suggest you post your query here:
    https://www.excelguru.ca/forums/foru...rm)-amp-M-code
    It's a site owned by Ken Puls, author of M is for (Data) Monkey, which is a Power Query primer. That forum linked-to above is dedicated to Power Query and it's where you'd stand a very good chance of getting some really good advice and some very slick/elegant solutions.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  12. #12
    Good Morning p45cal

    I am so very grateful for your assistance. Thank you so so much - will keep you posted on the progress

    Mark

Posting Permissions

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