+ Reply to Thread
Results 1 to 24 of 24

macro to put data in relevant groups

  1. #1
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    macro to put data in relevant groups

    hi guys

    im looking for some help please to auto fill data into specific areas of spreadsheet based on the day

    please note i have attached spreadsheet

    the data in columns h:n are auto populated and sorted from another spreadhseet

    so what i would like to do with a button is this ( if possible )
    in h11:n16 there are 2 days that correspond to MONDAY
    what i need the macro to do is move that data ( as per heading ) to mondays range which is b13:f22

    column a can be ignored as this looks up column b and returns the account code

    and also to do the same for corresponding days

    can anyone help please?
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: macro to put data in relevant groups

    all help would be gratefully received please

  3. #3
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,163

    Re: macro to put data in relevant groups

    Before copying the data would columns B:F be blank or would there be existing data?

    Also, should the macro check if the number of rows for Monday are enough to hold the data or will they always be enough and we only need to copy paste?

    If there is existing data in columns B:F, should it be deleted or should the data be copied below the existing data in columns B:F?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: macro to put data in relevant groups

    Hi Arlu and many thanks for your reply

    In answer to your questions
    the cells between b13 and f77 will be blank

    ideally the macro would check there is sufficient rows but if not insert rows to cover

    many thanks

  5. #5
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,163

    Re: macro to put data in relevant groups

    Would it be fine with you if the rows 13:77 are completely cleared (Days of the week also cleared) and the data from the right side is input freshly, after leaving about 2-3 rows between each day of the week?

  6. #6
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: macro to put data in relevant groups

    the days od the week are pretty important but if you have worked something out i will gladly appreciate it and take a look.
    thank you

  7. #7
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,163

    Re: macro to put data in relevant groups

    Also would it be fine with you if the days of the week in column I and the dates of the week in column A are spelt the same?

  8. #8
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: macro to put data in relevant groups

    Hi Arlu
    Yes that would be fine

  9. #9
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,163

    Re: macro to put data in relevant groups

    So should we consider that the data in column I would be like MONDAY, TUESDAY or would it be the other way round?

    Sorry these are small questions but required for the code.

  10. #10
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: macro to put data in relevant groups

    Monday, tuesday, wednesday is fine

  11. #11
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,163

    Re: macro to put data in relevant groups

    Try this code -

    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose Vew | Macros
    Select a macro in the list, and click the Run button

  12. #12
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: macro to put data in relevant groups

    doesnt do anything arlu
    Attached Files Attached Files

  13. #13
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: macro to put data in relevant groups

    Here's another..

    Press the "Click" button in the attached file..

    Please Login or Register  to view this content.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,163

    Re: macro to put data in relevant groups

    You need to ensure that the Monday, Tuesday in columns I & A match.

    I didnt clear out any columns but did a copy paste. Ensure that there is no data in B3:F77

  15. #15
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: macro to put data in relevant groups

    APO

    that works well with the exception that the first row runs next to the day so i cannot see the account number

  16. #16
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: macro to put data in relevant groups

    ARLU THATS BRILLIANT
    THANK YOU
    but does have a flaw see attached spread sheet after i have run your code
    Attached Files Attached Files

  17. #17
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: macro to put data in relevant groups

    oopss.. minor change..

    Change this line to:

    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: macro to put data in relevant groups

    apo

    just for arguments sake if i had
    15 days with monday what would happen ?

  19. #19
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: macro to put data in relevant groups

    if i was to allow 20 lines under each day
    would i need to alter the code in anyway?
    it would be easier to hide the empty rows after than trying to insert as im going
    and 20 would be enough

  20. #20
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: macro to put data in relevant groups

    yeap.. you could have 20 row sunder each day.. no change in code needed..

    If thats fine then you're good to go.. if not.. just need to code it to insert rows as it goes..

  21. #21
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: macro to put data in relevant groups

    I am more than happy to hide rows as i go
    thank you both so much for giving up some of your time to help me
    it really is appreciated

    thank you

  22. #22
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,163

    Re: macro to put data in relevant groups

    I cleared the data in column B:F and ran the macro after keeping 20 rows in between each day of the week and it worked fine.

  23. #23
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: macro to put data in relevant groups

    HI APO
    SEEMS I SPOKE TO SOON
    GETTING A RUNTIME ERROR 91
    WITH THIS LINE HIGHLIGHTED cpyRng.Copy f.Offset(1, 1)
    I HAVE ATTACHED SPREADSHEET AGAIN FOR YOU TO SEE MAYBE WHY THIS HAS HAPPENED
    Attached Files Attached Files

  24. #24
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: macro to put data in relevant groups

    Sorry.. thats me being to hasty (missus in background wanting to watch a movie...lol)..

    Please Login or Register  to view this content.

+ 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] Macro to display data from the last 7 days only and to move relevant comments
    By Brumbot in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 12-31-2021, 12:41 AM
  2. Macro button to move data to relevant worksheets
    By Tigger118 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-29-2013, 04:54 PM
  3. macro for Extracting relevant data from an excel database
    By cbhawsar in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-22-2011, 11:50 AM
  4. Macro to sort data,match and paste in to relevant sheets on same workbook
    By Benjamin2008 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-08-2009, 07:58 AM
  5. Macro Help:summary of the relevant data
    By H.S in forum Excel General
    Replies: 1
    Last Post: 03-12-2005, 05:06 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