+ Reply to Thread
Results 1 to 17 of 17

Copy cells to new worksheet based on month, to next blank row

  1. #1
    Registered User
    Join Date
    03-20-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    10

    Copy cells to new worksheet based on month, to next blank row

    Hi all, long time user, although newly registered.

    Although Copy + Paste and Copy to New Row are fairly common questions I find, I am having trouble getting it to fit to what I want.

    In the attached example, I have three sheets: 'TBC', 'Bland Anna' and 'Coates Beth'.

    I would like to search in columns E and F (no dates will be present until row 5, but as rows 1 through 4 will not contain dates, it may be easier/involve less code to just search the columns) for a specific month, e.g. for this example search for any dates that are in January.

    I would then like to copy the data in columns D through G that correlate to said date, and then paste onto sheet TBC on the next available row, not to overwrite.

    The macro in question would need to go through every sheet and perform the above action, although there are only 2 sheets in this example, the live workbook will likely contain 100+ sheets containing employee sick leave.

    Furthermore, and this is something I'm not sure would be feasible, everytime it copies data, I need it to pull across the name of the individual. This part is slightly more complicated as the names on the sick leave sheets are based at E2 and G2, whereas it needs to copy the names to B and C. Moving the name information from E2 and G2 would make sense to make copying easier, there are other sheets that I have excluded as they are not relevant here, but which are used elsewhere.

    If this is not possible, I will have to look into reworking the layout of the information to make it feasible.

    Thanks in advance.

    month copy test.xlsm

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy cells to new worksheet based on month, to next blank row

    Welcome to the Forum. Try:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    03-20-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Copy cells to new worksheet based on month, to next blank row

    Many thanks for your quick reply, it works a treat.

    If I understand correctly, the:
    Please Login or Register  to view this content.
    will check any sheet that is not named 'TBC'? So if i wanted the macro to not check multiple sheets, e.g. I had another sheet named "TBA". could i do:
    Please Login or Register  to view this content.
    Or is there a better way of doing this?

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy cells to new worksheet based on month, to next blank row

    You're welcome. Glad to help out and thanks for the feedback. Yes you are correct, and I don't know of a better way to go. Please comply with Forum Rule No. 9.


    9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

  5. #5
    Registered User
    Join Date
    03-20-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Copy cells to new worksheet based on month, to next blank row

    Sorry, hopefully an easy one.

    I've applied this to the main workbook, and if it comes to a sheet where there is no data on row 5, it returns an error. It returns this line on the debugger:
    Please Login or Register  to view this content.
    Sorry, should have mentioned that there is a possibility of there being no data, but it didnt cross my mind at the time of writing.

  6. #6
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy cells to new worksheet based on month, to next blank row

    Try:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    03-20-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Copy cells to new worksheet based on month, to next blank row

    Thanks for the suggestion, however it stills come back with an error reporting the same line I mentioned above.

  8. #8
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy cells to new worksheet based on month, to next blank row

    I can't duplicate the error on the sample data you provided. Do you spaces or text in any of the cells?

  9. #9
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy cells to new worksheet based on month, to next blank row

    Try:

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    03-20-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Copy cells to new worksheet based on month, to next blank row

    Marvelous, it works! sorry for the delayed reply. I am ever so grateful for your time and solution.

  11. #11
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy cells to new worksheet based on month, to next blank row

    You're welcome. Glad to help out and thanks for the feedback. Please mark this thread as solved.

  12. #12
    Registered User
    Join Date
    03-20-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Copy cells to new worksheet based on month, to next blank row

    Sorry to have to bring this up again.

    I wonder whether you're able to edit it so that it searches not only in Column E but also in column F?

    I have been away a week and have only just gotten back to testing the workbook further and noticed that if 'January', for example, appears in the second column, and not in the first, then it isn't copied onto the 'TBC' sheet.

    Any help would be greatly appreciated, I have tried editing it myself to search through F, but with no luck.

  13. #13
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy cells to new worksheet based on month, to next blank row

    Maybe:

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    03-20-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Copy cells to new worksheet based on month, to next blank row

    Thanks for the quick reply.

    Although that is now picking up the entries that have January in column "F", it is duplicating entries.

    e.g. If Catherine Bland had two sickness incidents, first incident 'first day of absence' was on 31st Dec with the 'last day of absence' on the 2nd of Jan, and then the second incident 'first day of absence' on the 30th Jan, with the 'last day of absence' on the 31st Jan, I would get one entry for the first incident, and then two entries for the second incident.

  15. #15
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy cells to new worksheet based on month, to next blank row

    OK try:

    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    03-20-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Copy cells to new worksheet based on month, to next blank row

    Brilliant, thats works perfectly, thank you for all your time.

  17. #17
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy cells to new worksheet based on month, to next blank row

    Welcome, glad to help out.

+ 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. Copy Non-Blank Cells to Another Worksheet
    By snapfade in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-10-2013, 02:13 PM
  2. [SOLVED] How to fill only blank cells based on another worksheet
    By meprad in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-26-2013, 04:13 AM
  3. [SOLVED] vba to copy non blank cells and paste to other worksheet
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-18-2013, 06:22 PM
  4. Copy certain cells from one worksheet to another worksheet with blank rows
    By pmarsella in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-06-2009, 07:57 AM
  5. Copy non blank cells to another worksheet
    By JD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-31-2006, 09: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