+ Reply to Thread
Results 1 to 21 of 21

split data across multiple sheets based on month

  1. #1
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    split data across multiple sheets based on month

    hello
    I search way to split data based on month across multiple sheets .
    the split data begins from row5 in COL A,B,C
    the result should begins col F in multiple columns based on month withe the borders and formatting as in my result each month should be individual.
    with considering I create a new sheets and the data increases ,sometimes changes the month in col B . it should update data for the new month and delete from old month for instance when run macro in first time and split based on month and return changing the month in COL B should add changing to a new month and delete from old month I put some result in some sheet to understand my Idea . so I would the macro is dynamic
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: split data across multiple sheets based on month

    As I always do with situations like this, why complicate matters by identifying new data in a file when it's simpler to delete all the old data and add the complete new file.

    That avoids any chance of either missing or duplicating data and give you a neat database. You can then use a standard template sheet to which you can filter a particular month if you wish, or better still just use a Pivot Table plus some Slicers to filter and analyse the data.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data across multiple sheets based on month

    I don't think to need pivot table because the data depends on the all sheets then should do by vba

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

    Re: split data across multiple sheets based on month

    vba
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data across multiple sheets based on month

    @jindon this is awesome! but I would that for all sheets not only the first

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

    Re: split data across multiple sheets based on month

    Change to
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data across multiple sheets based on month

    @jindon great! thanks so much for your solution

  8. #8
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data across multiple sheets based on month

    @jindon I no know if you can help me to create hyperlink and open my files names . all the items are existed under column(NAME) should be hyperlink
    my directory is "C:\Users\leopar\Desktop\report" if you can do that should be in post#6
    thanks again

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

    Re: split data across multiple sheets based on month

    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data across multiple sheets based on month

    thanks . but it gives error in this line
    HTML Code: 
    application defined or object defined error
    note: in my directory has many subfolders and when hyperlink the files also should open when press any file
    thanks for your assistance

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,407

    Re: split data across multiple sheets based on month

    If you want further help, I'd advise you to remove the SOLVED tag for now.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

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

    Re: split data across multiple sheets based on month

    Chang
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    If you don't know the correct folder path for each file then you can not add the hyperlink.

  13. #13
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data across multiple sheets based on month

    thanks . I check the path and I use right click the folder and copy the path and paste to your code to overcome mistake actually the error is gone , but I can't open any file has hyperlink

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

    Re: split data across multiple sheets based on month

    Quote Originally Posted by Alaa-A View Post
    note: in my directory has many subfolders and when hyperlink the files also should open when press any file
    thanks for your assistance
    As I said earlier, you need to to FULL path including file name and extension.
    e.g
    C:\Users\leopar\Desktop\report\mysubfoldername\xxx.xlsx while xxx.xlsx should be in each cell in Name column.

  15. #15
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data across multiple sheets based on month

    ahh , sorry I don't focus good
    even that there is a problem when add the updating for the code . it just apply for active sheet when split data in first sheet it split the same data and copy to the second sheet
    may be this is not clear please see first sheet and the second you will understand what I mean.
    Attached Files Attached Files

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

    Re: split data across multiple sheets based on month

    I don't understand what you are trying to say.

    What 's the problem?

  17. #17
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data across multiple sheets based on month

    the code in post#6 it split the data in col C based on month in COL B each sheet individually .
    the pics1 is ok but the pics 2 is wrong I highlighted by mark blue as you see in COL H ,M not matched as in COL C and it doesn't show in month MAR .
    if you note the wrong data are taken from the first sheet . I tested the code when active sheet1 . this is what happens as in pictures and also when activesheet2 . the data will be wrong in sheet1 as what happens in sheet1 by taken data from sheet2 to sheet1 .
    it should split based what is in existed in each sheet individually.
    Attached Images Attached Images

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

    Re: split data across multiple sheets based on month

    It IS picking 3 rows for MAR.
    Attached Files Attached Files

  19. #19
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data across multiple sheets based on month

    I clear the data and split again . the problem still continues as in the pictures. where I mistook?
    Attached Files Attached Files

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

    Re: split data across multiple sheets based on month

    OOps, I don't know why it dropped off when I posted the code in #6.
    So, we were talking about different codes...
    Please Login or Register  to view this content.
    Should be
    Please Login or Register  to view this content.

  21. #21
    Forum Contributor
    Join Date
    05-24-2021
    Location
    Palestine
    MS-Off Ver
    2019
    Posts
    280

    Re: split data across multiple sheets based on month

    fantastic !! the code works excellently
    million thanks for your a huge assistance !

+ 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. Help with copying data from master sheet to multiple sheets based on month
    By maym in forum Excel Programming / VBA / Macros
    Replies: 63
    Last Post: 01-09-2023, 01:58 AM
  2. [SOLVED] Data split in different sheets and amount to be mentioned in the same month
    By shyampanda in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-13-2021, 02:45 PM
  3. [SOLVED] Split list into multiple sheets based on criteria (For each/next)
    By nymanns in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-10-2017, 04:16 PM
  4. [SOLVED] Need VBA code to match the value and split the data in the multiple sheets based on column
    By Giri.hb in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-04-2016, 09:06 AM
  5. Need Macro code to split data split in 7 sheets based on variable rows in column A
    By Alija_21 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-31-2015, 08:03 AM
  6. [SOLVED] Split excel data into multiple sheets based on Employee ID
    By Preeti1309 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-21-2014, 02:33 AM
  7. Replies: 1
    Last Post: 04-12-2014, 04:03 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