+ Reply to Thread
Results 1 to 5 of 5

Copy unique rows to another sheet

  1. #1
    Forum Contributor
    Join Date
    08-10-2006
    Location
    The Netherlands
    MS-Off Ver
    Office for Mac 2016; MS Office 2016 for Windows
    Posts
    110

    Copy unique rows to another sheet

    Hi Experts,

    Attached file has a few sheets which (need to) do the following:
    Sheet "Data"; contains the data to work with. This will be updated each week by previous week (or multiple weeks) data
    Sheet "Calculation": is column A:L from sheet "Data" copied into column A:L, column M:Q contains formulas
    Sheet "Overtime", "Clock Adjusted" and "Clocktime" needs to be filled based on the below.

    I've already created a macro which copies lines with overtime (Column M <> 0 in sheet "Calculation") to the sheet "Overtime".


    What I still need and what I can't get to work is:
    1. a macro (new or added to the excisting one) which copies column A:L from sheet "Data" to sheet "Calculation" column A:L (thus leaving the formulas in column M:Q intact)

    2. a macro (new or added to the excisting one) which copies the lines from sheet "Calculation" to sheet "Clock Adjusted" if cell value in column N and/or column O <> 0

    3. a macro (new or added to the excisting one) which copies the lines from sheet "Calculation" to sheet "Clocktime" if cell value in column P and/or column Q <> 0

    4. For all 3 sheets the macro need to add only the unique lines, so don't copy lines which are already there when the macro is run again.




    I hope that I've explained the above a bit clear for you, if not, please do let me know.

    Thanks for all your time and effort!!
    Peter

    template.xlsm

  2. #2
    Registered User
    Join Date
    09-26-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Copy unique rows to another sheet

    Hi there Bax,

    Your sheet doesn't look too complicated, still has some columns left for logging.

    I would suggest that you will use a column to log the date (time stamp) that a certain line is copied to other sheet.
    This way your macro only needs to run through on one sheet at a time and not make cross references between sheets (which takes time / higher risk on errors / and makes things complicated where they don't have to be).

    With logging the date you can easily check when macro has been ran and data has been moved.

    After copying the data you can copy the formulas (or just pull down far enough to cover next weeks of data)

    If you still cant get it to work you know where to find the forum

    Cheers,
    Demeter

  3. #3
    Forum Contributor
    Join Date
    08-10-2006
    Location
    The Netherlands
    MS-Off Ver
    Office for Mac 2016; MS Office 2016 for Windows
    Posts
    110

    Re: Copy unique rows to another sheet

    Hi Demeter,

    Creating a stamp to log which line was already copied, also crossed my mind. I've seen some VBA code on this forum which would put a '1' in the last cell of a row when it was copied to the other sheet. In that case, the macro would only need to look at the rows which doesn't have a '1' in the last cell. However, not sure how to wrap this into the excisting macro. Any idea?

    Will still need the macro to determine which and copy these selected lines to the various sheets.

    Thanks again for your time and expertise!
    Kind regards,
    Peter

  4. #4
    Registered User
    Join Date
    09-26-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Copy unique rows to another sheet

    Did change your code a bit.
    It copies the rows without time stamp from sheet data to calculation.

    Think rest you will figure out.

  5. #5
    Forum Contributor
    Join Date
    08-10-2006
    Location
    The Netherlands
    MS-Off Ver
    Office for Mac 2016; MS Office 2016 for Windows
    Posts
    110

    Re: Copy unique rows to another sheet

    Great, thanks Demeter. Now that I see the code, I understand the logic behind it and can work from here.

    Thanks,
    Peter

+ 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. compare sheet 1 with sheet 2 and copy unique values in sheet 1
    By irfanparbatani in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2014, 07:38 PM
  2. Replies: 2
    Last Post: 06-11-2014, 11:14 AM
  3. Compare 2 sheets pick up unique and copy rows to another Sheet
    By dungoc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-23-2013, 10:08 AM
  4. Copy rows with unique values to another sheet
    By vmed in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-29-2006, 03:20 PM
  5. copy unique rows
    By Newbie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-04-2006, 07:10 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