+ Reply to Thread
Results 1 to 18 of 18

VBA Auto Split and Merge Report Data

  1. #1
    Registered User
    Join Date
    08-01-2018
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2013
    Posts
    12

    VBA Auto Split and Merge Report Data

    Hi all,

    Really scratching my head on this one, some help would be great!

    I currently get three excel files generated which have different raw report data for all regions. My end goal is to email each region a file that has three sheets, and on each sheet is the relevant data from the raw report files.

    I'm trying to create some VBA code that will go through each of these three raw report files, separate out the data relevant to each region (column D) and then create new workbooks which have three sheets for each for each region with the different report data on each one and then attach it to an email ready to go.

    I know this sounds confusing so I have attached some mock up files, starting with RawOne, RawTwo and RawThree and ending with AucklandReports, ChristchurchReports and WellingtonReports (couldn't attach Wellington due to the limit but you will get the idea with Auckland and Christchurch).

    The issue is I have over 40 regions and each region has over 100 rows of data so to do this manually each month is very time consuming.

    Hopefully this makes sense, please let me know if otherwise - thanks!
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: VBA Auto Split and Merge Report Data

    crosssposted: https://www.mrexcel.com/board/thread.../#post-5596500

    Rule 03: Cross-posting Without Telling Us

    Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

    Post a link to any other forums where you have asked the same question.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Read this to understand why we ask you to do this.

    I have added the crosspost reference for you today. Please comply with this and all our rules in the future
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    08-01-2018
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: VBA Auto Split and Merge Report Data

    Hi Adam,

    Seems my original reply has disappeared so apologies if this comes through twice.

    So far I have split the raw data into separate workbooks for each region, so for the above example I would have three folders each with an Auckland, Christchurch and Wellington workbook containing one sheet each.

    The next part I was trying to do is search through the subfolders for any duplicated sheet names and combine them to get each workbook with three sheets.

    I guess my specific questions are am I on the right track or is there a more simple way of doing this, and is there an example of VBA code where subfolders are searched and all duplicate sheet names are combined into one workbook (bit of a stretch I know)?

    Cheers

  4. #4
    Registered User
    Join Date
    08-01-2018
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: VBA Auto Split and Merge Report Data

    Oops, sorry Alan! Will remember for next time

  5. #5
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: VBA Auto Split and Merge Report Data

    The answers are...

    1) for code to search folders and subfolders in a Windows system, see the post by me here on this board under The forum called tips and tutorials.

    2) to search sheets and combine them based on duplicate data, that is simple code that uses the workbook and worksheets objects in excel. See the internet for that anywhere.

  6. #6
    Registered User
    Join Date
    08-01-2018
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: VBA Auto Split and Merge Report Data

    Thanks for the direction Adam, I have been trying the following code to open one workbook in one subfolder at a time (assign it to CurrentWB) and then open the next file in the folder (SearchFile), see if there is a sheet with the same name and copy it to CurrentWB if there is, otherwise close it and open the next file if there isn't. I'm currently getting stuck on the CurrentWB part, where I'm trying to use the For Each file loop to do one file at a time but it opens everything in the folder instead.. This is what I have so far:

    Please Login or Register  to view this content.
    Apologies if this is way off, I haven't tried doing multiple loops before.

    Thanks again!
    Last edited by PandahNZ; 11-30-2020 at 06:48 PM.

  7. #7
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: VBA Auto Split and Merge Report Data

    couple things. it's opening all the books because your close statement is never being read. and the reason for that is because you are issuing DIR() on SearchFile, however searchFile NEVER is the full path of an actual file name. you did this:
    Please Login or Register  to view this content.
    that creates this:
    Please Login or Register  to view this content.
    you need this:
    Please Login or Register  to view this content.
    when you do that, you'll loop every file name using dir(). Dir() checks for the existence of files with the spec'd extensions. so you're doing things right in that regard. you can also do it simply by looping files 3 nestings down inside of GETFOLER() and SUBFOLDER.

    I did not check the rest of your code.

  8. #8
    Registered User
    Join Date
    08-01-2018
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: VBA Auto Split and Merge Report Data

    Hi again Adam,

    Thank you for the help!

    I tried to follow your advice use nested loops instead, getting much closer but appears the issue I have run into now is when the code finds a workbook with the same name in a different folder the
    Please Login or Register  to view this content.
    doesn't work (assuming because the original workbook with the same name is already open).

    Do you have any pointers for getting around this?

    Here is my code so far:
    Please Login or Register  to view this content.
    Thanks again

  9. #9
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: VBA Auto Split and Merge Report Data

    well what I don't understand is why you are looping files in the same dir more than once. WHY? this code:
    Please Login or Register  to view this content.
    is doing the same thing as this code, which is technically inside the block above:
    Please Login or Register  to view this content.
    we have gone through this quite a bit at this point. can you post a shot of your dirs? what you want to get from them? your source dir and the files in it? thanks.

  10. #10
    Registered User
    Join Date
    08-01-2018
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: VBA Auto Split and Merge Report Data

    Okay so in "overall" I have the three folders with each report type in it and a master sheet that contains the VBA.

    Then in each report folder are the various regions which have the same name workbooks and worksheets but contain slightly different data.

    I'm trying to take all the sheets with the same names from each of the three folders and have them all in one workbook. The final workbook can just be one of the originals with the extra two sheets or an entirely new workbook, doesn't really matter.

    I was trying to use that 2nd loop to compare all the files to one file at a time, probably not the best way of doing things by the sound if it though!

    Thanks again for your time, much appreciated.
    Attached Images Attached Images

  11. #11
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: VBA Auto Split and Merge Report Data

    the first thing I always saw wrong was this code:
    Please Login or Register  to view this content.
    PATH is not declared. did you noticed that? furthermore, you are literalizing it. the way you're doing it, it should be a dir path, not just PATH. unless you're wanting to capture the ENVIRON variable, which I doubt.

    ok, so I ran the macro recorder for the task that you are missing. it literally was 2 lines on a test:
    Please Login or Register  to view this content.


    thus....this is what you want, right? and, see images below....
    Please Login or Register  to view this content.
    the only thing that still doesn't make sense is this:
    Please Login or Register  to view this content.
    you can't have more than one sheet in an XL book with the same name for obvious reasons. the indexing by excel would get confused, not to mention raising a conflict of interest.

    that code is just a modification of your last block posted. try it and see what happens. change the literals to make it work for you. first though, manually create a new book like I did. the result of my test can be seen in image 3 below:
    Attached Images Attached Images
    Last edited by vba_php; 12-01-2020 at 01:14 AM.

  12. #12
    Registered User
    Join Date
    08-01-2018
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: VBA Auto Split and Merge Report Data

    Thanks again for your time Adam!

    Sorry I should have mentioned I just typed PATH in there to try and keep personal details to a minimum as this is on a work laptop, normally that has the actual path as you have entered youself.

    This result was pretty close, I just need to end up with separate workbooks for each group of sheets (the sheets currently have exactly the same name hence why I was using x and x + 1 in my code to increment a number at the end).

    So for instance, using the images I posted above where there are 3 folders each with 4 files with the same names - I am trying to end up with 4 workbooks. Each of these 4 workbooks would have 3 sheets and each of those sheets originally have the same name (i.e. Region One) so would now have Region One 1, Region One 2 and Region One 3.

    Sounds confusing I know.. but manually is a massive task which is why I'm putting a lot of time in this and grateful that you are as well.

    Please let me know if this doesn't make sense and I will create more snap shots and examples.

    Many thanks!!

  13. #13
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: VBA Auto Split and Merge Report Data

    No no I think it makes perfect sense. But unfortunately I'll be working all night long in the overnight hours to finish something for someone else. so I won't be able to finish this for you until tomorrow at the earliest. But I will bookmark it just in case someone else doesn't chime in and help you out. the last changes that you need literally are so simple it should take someone like me or the others here 5 minutes at the most to write it and test it based on the code that's already been posted in this thread. Can you give it a shot yourself? Based on what I just posted in my last response? if not I can get back to you tomorrow or a little later.

  14. #14
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: VBA Auto Split and Merge Report Data

    here's what you need, I assume:
    Please Login or Register  to view this content.
    and by the way, this STILL doesn't make any sense. I think you've been working a little too much and getting lost in information:
    Quote Originally Posted by PandahNZ View Post
    So for instance, using the images I posted above where there are 3 folders each with 4 files with the same names - I am trying to end up with 4 workbooks. Each of these 4 workbooks would have 3 sheets and each of those sheets originally have the same name (i.e. Region One) so would now have Region One 1, Region One 2 and Region One 3.
    let me know if this works. see images below for what happened when I ran the test:
    Attached Images Attached Images

  15. #15
    Registered User
    Join Date
    08-01-2018
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: VBA Auto Split and Merge Report Data

    Thanks for the help Adam, this is getting much closer now - only issue is that it currently combines all the sheets from each folder into one workbook, as opposed to taking the sheets with the same name from each folder.

    Looking at the images I posted earlier, you can see each of the three folders has a file named Region One, Region Two, Region Three & Region Four - so in total there are three of each of these files. So I need four workbooks where one workbook has three Region One sheets (with incrementing numbers of course), the next workbook has three Region Two sheets and so on.

    I was also unable to get the TestWB.SaveAs line working, getting a run time error 1004 (Method 'SaveAs' of object '_Workbook' failed) but I'm thinking that has something to do with my end so still looking into that.

  16. #16
    Registered User
    Join Date
    08-01-2018
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2013
    Posts
    12
    Haha no worries.

    Yeh so each file has one worksheet, and that work sheet has the same name as the file.

    So RegionOne workbook will have one sheet called RegionOne and so on. And there is one Region One per folder and 3 folders so will end up with one workbook that has three RegionOne sheets. Hopefully that clears things up! Sorry for all the confusion.

    Cheers

  17. #17
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: VBA Auto Split and Merge Report Data

    alrighty then, makes perfect sense. this will do it:
    Please Login or Register  to view this content.
    I think you can probably algorithmic that solution to death to better it, but I don't really have the time to care about that. that should work fine. I had to use a temp workbook and throw sheets in and out of it because of all of the books you have named the same. you can't open one book at a time with the same name. let me know.

  18. #18
    Registered User
    Join Date
    08-01-2018
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: VBA Auto Split and Merge Report Data

    Ah thanks so much Adam!

    Works perfectly - I had to activate Sheet1 on TestWB before deleting and saving for some reason otherwise it would break when running in auto but otherwise does everything it needs to!

    I spent some time working through manually line by line so I understand everything going on and I never would have figured this out so I have learnt a lot with this.

    Thanks again for all your time on this, much appreciated!

    Cheers

+ 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. [SOLVED] Split merge doc and save in any of the merge field data
    By bmbalamurali in forum Word Formatting & General
    Replies: 5
    Last Post: 03-23-2018, 09:18 PM
  2. [SOLVED] vba code to merge and split specific data
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-26-2017, 03:49 AM
  3. Split single report into several customized report
    By calvinle in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-28-2017, 11:56 PM
  4. Merge data in report sheet in excel then print to one pdf file
    By djon5020 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-27-2015, 07:50 PM
  5. split data from report into separate areas.
    By wentom in forum Excel General
    Replies: 16
    Last Post: 05-28-2012, 04:58 AM
  6. change merge code to split the data
    By bujji1305 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-23-2012, 02:28 AM
  7. [SOLVED] VLookup to merge Access Query data into Excel Report
    By Vira-SJH in forum Excel General
    Replies: 0
    Last Post: 01-10-2006, 03:25 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