+ Reply to Thread
Results 1 to 17 of 17

Macro to split master file by key field where key field is on all three sheets

  1. #1
    Registered User
    Join Date
    03-27-2013
    Location
    Wiltshire, England
    MS-Off Ver
    Excel 2007
    Posts
    75

    Macro to split master file by key field where key field is on all three sheets

    Hello,

    I enclose a file which had three active sheets of data. On each sheet is the key column called "location"
    I require a looping macro that will loop through the master file and place the data for each location into individual files where each file will be the location name.
    So each finished location file will have three sheets with the same sheet names as the master file, but will only contain the data for its own location.
    I will always place the master file in the directory C:\Documents before starting the macro and it will be fine for the finished location files to be placed here as well.

    This is just a small example file of a larger file which contains about 2,000 rows across 40 locations.
    The number of columns and rows on each sheet will vary each week.

    If you can help me I would really appreciate it.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: Macro to split master file by key field where key field is on all three sheets

    So to be clear: you end up with one file for each location? (in the given example: 4 files)
    Is it (ever) possible that the number of cities in the sheets Temp Payments and Structure Costing higher is than in Depot Report?
    Is it coincidence that the number of rows in the sheets Temp Payments and Structure Costing are the same?
    B/R
    Erwin
    Last edited by Eastw00d; 03-05-2020 at 12:32 PM.
    I started learning VBA because I was lazy ...
    Still developing.... being more lazy...

  3. #3
    Registered User
    Join Date
    03-27-2013
    Location
    Wiltshire, England
    MS-Off Ver
    Excel 2007
    Posts
    75

    Re: Macro to split master file by key field where key field is on all three sheets

    Hello Erwin,

    Yes, one file per location, and the number of locations may differ from week to week.
    No the number of cities in temp payments and structure costing will either equal or be less than the depot report.
    Yes, it is a coincidence that the number of rows in Temp Payments and Structure Costing are the same, in reality they are highly likely to have difference number of rows.

    Thanks for your support on this.
    Jonathan

  4. #4
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: Macro to split master file by key field where key field is on all three sheets

    I am working on it, have a little patience :-)

    B/R
    Erwin

  5. #5
    Registered User
    Join Date
    03-27-2013
    Location
    Wiltshire, England
    MS-Off Ver
    Excel 2007
    Posts
    75

    Re: Macro to split master file by key field where key field is on all three sheets

    Thank you Erwin for taking time to work on it.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,446

    Re: Macro to split master file by key field where key field is on all three sheets

    This is how I understand and for the data uploaded.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: Macro to split master file by key field where key field is on all three sheets

    Sorry Jonathan for keeping you waiting but had some obligations elswhere :-)
    as below the code and the attachment.
    The only liberty i took, is sorting the data in the code in the original file.
    Please Login or Register  to view this content.
    @ Jindon, also very nice code, I am still learning :-)
    B/R
    Erwin
    Attached Files Attached Files
    Last edited by Eastw00d; 03-06-2020 at 11:46 AM. Reason: typo

  8. #8
    Registered User
    Join Date
    03-27-2013
    Location
    Wiltshire, England
    MS-Off Ver
    Excel 2007
    Posts
    75

    Re: Macro to split master file by key field where key field is on all three sheets

    Thank you Erwin for the work you did on this and apologies for the delay in acknowledging your efforts.

    Thanks
    Jonathan

  9. #9
    Registered User
    Join Date
    03-27-2013
    Location
    Wiltshire, England
    MS-Off Ver
    Excel 2007
    Posts
    75

    Re: Macro to split master file by key field where key field is on all three sheets

    Hello Jindon, thank you for the work you did on this and apologies for the delay in acknowledging your efforts.

  10. #10
    Registered User
    Join Date
    03-27-2013
    Location
    Wiltshire, England
    MS-Off Ver
    Excel 2007
    Posts
    75

    Re: Macro to split master file by key field where key field is on all three sheets

    Just one question Jindon, the master file I gave you was using just row 1 as the fixed headers on each sheet. In reality my master file has fixed headers from rows 1 to 4 and the variable data each time starts on row 5. Therefore which line of your code do I need to change, so each sheet always keeps rows 1 to 4 as headers as the sheet header and the variable data starts on row 5.

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

    Re: Macro to split master file by key field where key field is on all three sheets

    Can not tell anything without seeing your actual heading.

  12. #12
    Registered User
    Join Date
    03-27-2013
    Location
    Wiltshire, England
    MS-Off Ver
    Excel 2007
    Posts
    75

    Re: Macro to split master file by key field where key field is on all three sheets

    Just one question Erwin, the master file I gave you was using just row 1 as the fixed headers on each sheet. In reality my master file has fixed headers from rows 1 to 4 and the variable data each time starts on row 5. Therefore which line of your code do I need to change, so each sheet always keeps rows 1 to 4 as the sheet headers and the variable data starts on row 5.

  13. #13
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: Macro to split master file by key field where key field is on all three sheets

    Hi Jonathan, please can you upload a sample of this workbook?

    Cheers
    Erwin

  14. #14
    Registered User
    Join Date
    03-27-2013
    Location
    Wiltshire, England
    MS-Off Ver
    Excel 2007
    Posts
    75

    Re: Macro to split master file by key field where key field is on all three sheets

    Hi Erwin,

    Please find attached an example, where as you can see pay date, company and pay frequency as well as the column headers take up the first four rows.
    Whatever appears on the first four rows of each sheet must remain the first four rows of each of the three sheets within each location file.

    I also note on your macro code provided earlier, there is reference to column H in a range. While my example does finish at column H, in reality the number of columns each week will be variable.

    Thanks for your help.
    Jonathan.
    Attached Files Attached Files

  15. #15
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: Macro to split master file by key field where key field is on all three sheets

    I had to reshuffle the macro, this is the endresult:
    Please Login or Register  to view this content.
    Cheers
    Erwin
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    03-27-2013
    Location
    Wiltshire, England
    MS-Off Ver
    Excel 2007
    Posts
    75

    Re: Macro to split master file by key field where key field is on all three sheets

    Hello Erwin,

    Thanks for your continued support on this, but I wanted the first four header rows to appear on each location file at the top of each sheet.
    I enclosed "Glasgow Example" file to show how a finished location file should look.

    Thanks
    Jonathan
    Attached Files Attached Files

  17. #17
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: Macro to split master file by key field where key field is on all three sheets

    Hi Jonathan,
    This should do the trick. Just replace the main procedure with this one:
    Please Login or Register  to view this content.
    Cheers
    Erwin

+ 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. Macro to split master file by key field where key field is on ll three sheets
    By picton2000 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-02-2020, 04:54 PM
  2. Replies: 0
    Last Post: 08-15-2016, 02:07 PM
  3. [SOLVED] PowerPivot: Merge Month Field and Year Field Into a Date Field
    By mo4391 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-17-2015, 11:59 AM
  4. Help! Creating summary sheets for a master multi-field budget
    By LP27 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-24-2014, 09:42 AM
  5. Replies: 2
    Last Post: 03-18-2014, 03:58 PM
  6. Replies: 0
    Last Post: 06-26-2012, 09:06 PM
  7. Replies: 3
    Last Post: 03-03-2012, 12:16 PM

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