+ Reply to Thread
Results 1 to 25 of 25

VBA Separate monthly data into weekly data

  1. #1
    Registered User
    Join Date
    08-31-2013
    Location
    Belgrade
    MS-Off Ver
    Excel 2010
    Posts
    17

    Lightbulb VBA Separate monthly data into weekly data

    Hello,

    I'm thinking about this all day long and didn't come to conclusion how can i solve it. Hope you can help me with this.

    What i need is a macro to separate (copy) montly data from one sheet into weekly data, creating new sheets for every week.

    For example, I have a file with data from 02.05.2013 to 27.05.2013. I need macro to copy whole raws from 27.05.2013 till 21.05.2013. to another (new) sheet (called Week1) and so on (from 20.05. to 14.05 to Week2 sheet, etc...) taking in account cell A1 as starting date.

    The dates i have in "montly" sheet (i call it monthly but it can contain data for 2-3 months) are in column A (descending order, containing only dates), and macro must take the first cell as a reference cell. (e.g. if A1 is 12.08.2013, it will start to separate data from that date). The last week will usually not be full week or it can contain only 1 day but that's ok.

    Hope i explained it well.

    Any thoughts? Maybe i'm missing somthing easy here.

    Thanks in advance.

    Links to same thread on other forums:

    http://www.vbaexpress.com/forum/show...to-weekly-data

    http://www.mrexcel.com/forum/excel-q...ekly-data.html
    Last edited by Vokas; 09-01-2013 at 03:04 PM.

  2. #2
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit - Win 11
    Posts
    907

    Re: VBA Separate monthly data into weekly data

    Cross-post:
    http://www.vbaexpress.com/forum/show...to-weekly-data
    http://www.mrexcel.com/forum/excel-q...ekly-data.html

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA Separate monthly data into weekly data

    Hi Vokas,

    Check out the DateValue Function
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  4. #4
    Registered User
    Join Date
    08-31-2013
    Location
    Belgrade
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: VBA Separate monthly data into weekly data


    So?

    Didn't know it's prohibited. Just need the solution/idea/tip as soon as possible, i'm working some material for my boss, i would not post it if i have it...

    I'll check datavalue function meanwhile.
    Last edited by Vokas; 08-31-2013 at 05:43 PM.

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: VBA Separate monthly data into weekly data

    So?

    Didn't know it's prohibited.
    If you had read the Forum rules you may have known...

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    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.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
    HTH
    Regards, Jeff

  6. #6
    Registered User
    Join Date
    08-31-2013
    Location
    Belgrade
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: VBA Separate monthly data into weekly data

    Ok, my mistake, but be honest, who is reading forum rules before posting? I know, it's wrong, don't get me wrong, I'm just in a rush right now, wanted some answer instantly.

    I'm with this all day, working something for my boss, if i have solution wouldn't posted at several forums. And i'm newbiee at VBA. I'll ask admins of other forums to delete duplicate post if it's necessary.

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: VBA Separate monthly data into weekly data

    I bet you get more responce on your question if you follow the advice in the link below.

    http://www.excelforum.com/the-water-...-question.html

    Please also reply the status of your question on the other forums?
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  8. #8
    Registered User
    Join Date
    08-31-2013
    Location
    Belgrade
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: VBA Separate monthly data into weekly data

    No luck on other forums, just one code but it's not working...

    Attached is the example of sheet that I need to separate into weekly sheets.

    SeparateMintoW_1.xlsx

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA Separate monthly data into weekly data

    How do you want to group them? Sunday - Saturday, Monday - Sunday or by numbers first to seventh etc?

    Your definition post seems to want by numbers

    fr. 7/1/2013 has datevalue 41456 and its weekday is 2 or Monday

    now should we gather all the data thru next weekday 1 datevalue 41462 ??
    Last edited by xladept; 09-01-2013 at 02:57 PM.

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: VBA Separate monthly data into weekly data

    Hi xladept,

    Please wait for the OP to comply with post #5...

  11. #11
    Registered User
    Join Date
    08-31-2013
    Location
    Belgrade
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: VBA Separate monthly data into weekly data

    Quote Originally Posted by xladept View Post
    How do you want to group them? Sunday - Saturday, Monday - Sunday or by numbers first to seventh etc?

    Your definition post seems to want by numbers
    In example sheet we have data from 01-07-13 to 24-07-13. Want to group them like this: Week1 (all rows from 01-07-13 to 07-07-13); Week2 (all rows from 08-07-13 to 14-07-13); Week 3 all rows from 15-07-13 to 21-07-13), Week4 (all rows from 21. to 24 ) etc. The sheet will usually not start with 1st day in the month it can be some 15h or 25th...

    In my first post I stated that the dates are in descending order although here, in example sheet, they're in ascending order. Normally the cell a1 contains most recent data...
    Last edited by Vokas; 09-01-2013 at 02:54 PM.

  12. #12
    Registered User
    Join Date
    08-31-2013
    Location
    Belgrade
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: VBA Separate monthly data into weekly data

    Quote Originally Posted by jeffreybrown View Post
    Hi xladept,

    Please wait for the OP to comply with post #5...
    Not sure what do you mean. Rollis wrote the links to other forums in his message below my first post...

  13. #13
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: VBA Separate monthly data into weekly data

    You may think this is too harsh, but that is Rollis, not you...

    Is it Rollis' responsibility to comply with the rules for you or yours...?

  14. #14
    Registered User
    Join Date
    08-31-2013
    Location
    Belgrade
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: VBA Separate monthly data into weekly data

    Hope you're satisfied now. :D

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA Separate monthly data into weekly data

    Hi Vokas,

    Here's a routine - it works on the descending order file that you described (I resorted your sample):

    Please Login or Register  to view this content.
    Directions for running the routine(s) just supplied

    Copy the code to the clipboard

    Press ALT + F11 to open the Visual Basic Editor.

    Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

    Select “Module” from the Insert menu

    Type "Option Explicit" then paste the code under it

    And, you should be ready to go

    With the cursor between Sub and End Sub press F5 (F8 to Single Step)

    OR

    Press ALT + Q to close the code window.

    Press ALT + F8 then double click on the macro name

  16. #16
    Registered User
    Join Date
    08-31-2013
    Location
    Belgrade
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: VBA Separate monthly data into weekly data

    Works like a charm.

    Thanx thanx thanx!

  17. #17
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA Separate monthly data into weekly data

    You're welcome!

  18. #18
    Registered User
    Join Date
    08-31-2013
    Location
    Belgrade
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: VBA Separate monthly data into weekly data

    Can you rewrite the code so every time I run the macro it starts "clean"/"from zero" (first it deletes all sheets called "week 1/2/3/..." [if they exists] and then it creates new ones)?

    If I change the data in basic sheet and run the macro for the second time, it gives me an error that it cannot overwrite existing sheet.
    When i add command "On Error Resume Next" it creates new sheets but called Sheet X/y/z which is no use for me...

  19. #19
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA Separate monthly data into weekly data

    Hi Vokas,

    Try this:

    Please Login or Register  to view this content.
    And, Thanks for the rep

  20. #20
    Registered User
    Join Date
    08-31-2013
    Location
    Belgrade
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: VBA Separate monthly data into weekly data

    Had to modify a few things (like the name of the basic sheet and to add error handler after "DefBlock: If r = 0 then...") but it worked in the end.

    Thank you again.

  21. #21
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA Separate monthly data into weekly data

    You're welcome!

  22. #22
    Registered User
    Join Date
    08-31-2013
    Location
    Belgrade
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: VBA Separate monthly data into weekly data

    Sorry for bothering you again i experienced one more problem with code. (hope it's the last one :D)

    It does separate monthly data into weekly but only if the data is from the same month. If the basic sheet contains data from two months or, better say, if the basic sheet contains data from separate months the macro stop on the code:
    Please Login or Register  to view this content.
    Any suggestions?

  23. #23
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA Separate monthly data into weekly data

    Hi Vokas,

    This one counts down from the DateValue (my original suggestion):

    Please Login or Register  to view this content.
    Last edited by xladept; 09-06-2013 at 06:59 PM.

  24. #24
    Registered User
    Join Date
    08-31-2013
    Location
    Belgrade
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: VBA Separate monthly data into weekly data

    Great! Works perfectly! Thanx again xladept!

    Won't bother you more.

  25. #25
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA Separate monthly data into weekly data

    You're welcome - it wasn't a bother -it was fun

+ 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. Excel 2007 : How to convert weekly data to monthly data
    By kissanbhai009 in forum Excel General
    Replies: 3
    Last Post: 08-10-2017, 04:35 AM
  2. Weekly Data averaged into monthly data
    By Insert Name in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-09-2012, 04:55 AM
  3. Replies: 2
    Last Post: 07-13-2011, 04:02 AM
  4. aggregating weekly data into monthly
    By mattbessey in forum Excel General
    Replies: 3
    Last Post: 03-24-2010, 02:14 PM
  5. Replies: 1
    Last Post: 04-17-2009, 04:57 AM

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