1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code to cut and paste rows from one sheet to anothersheet based on condition

Discussion in 'VBA Macros' started by sarisri, Mar 16, 2018.

  1. sarisri

    sarisri New Member

    Messages:
    16
    Dear all,

    Let me put my request little more clear.

    I need the VBA code, if I click the button i would like to select the rows containing the values 'APCW2' (not exactly but only containing/starting with APCW2) in the column 'Emp Code' and cut and paste in the new sheet of the same workbook. This is what I look at exactly.

    The same xl file is attached.

    Pls help me out.

    UnitEmp Code
    AAPCW1940014484
    AAPCW1940014485
    AAPCW1940014486
    AAPCW1940014487
    AAPCW1940014488
    AAPCW2940002306
    AAPCW2940002312
    AAPCW2940002313
    AAPCW2940002314
    AAPCW2940002316
    AAPCW2940002319
    AAPCW2940002323
    AAPCW2940002325
    AAPCW2940002392
    AAPCW2940003215
    AAPCW2940003216
    AAPCW2940003218
    AAPCW2940003220

    Attached Files:

  2. vletm

    vletm Excel Ninja

    Messages:
    4,166
    sarisri
    Something like this...
    but not always to NEW SHEET!

    Attached Files:

    ThrottleWorks and Chirag R Raval like this.
  3. sarisri

    sarisri New Member

    Messages:
    16
    Thank you so much for your reply. Exactly worked what I intended.

    Just I need one more help from you, the rows selected based on the criteria are copying and pasting in other sheet perfectly. But I need, Cut and Paste the rows those selected, so that the first sheet should not having those rows.

    Also another request, could you give the elaborative comments (' comments) of each every line coded in VBA for my better understanding to try out in future based on that.

    Advance thanks.
  4. vletm

    vletm Excel Ninja

    Messages:
    4,166
    sarisri
    Next something like this ...
    it would include something that You would like to get.

    Attached Files:

    Chirag R Raval likes this.
  5. sarisri

    sarisri New Member

    Messages:
    16
    Excellently working which I needed exactly. Many thanks for your help.

    I would need one more VBA solution which I am facing, would post you soon.

    Once again my thanks.
  6. sarisri

    sarisri New Member

    Messages:
    16
    As I mentioned above, here is my other request for your support.

    Attached & shown below computer/in-built software generated xl sheet, I would like to do the below.

    Once I click the Button, VBA code would need to filter the 'Column - Shift' and select 'NoShift' rows and 'Weekly off' rows (both at a time) and delete those values (not the rows pls).
    And those empty NoShift rows need to be filled with the relevant 'Shifts' based on 'In Punch' and 'Out Punch' as well as 'Worked Hours' Columns.

    The Shifts criteria is as follows; the below are standard timings, but in actual, there can be variances in timings of in & out punch. You may find it once you open the attached xl file.

    1st Criteria Shifts (8 - 10 Worked Hours)
    A Shift - Inpunch time 6.00 AM - outpunch time 14.00; B Shift - 14.00 - 22.00
    C Shift - 22.00 - 6.00 AM; G Shift - 8.30 AM - 17.30 - all these worked hours are in between 8 hrs - 10 hrs.

    2nd Criteria Shifts (11- 15 Worked Hours)
    A Shift+ Extra Hrs; B + Extra Hrs ; C + Extra Hrs ; G + Extra Hrs

    3rd Criteria Shifts (Above 16 Worked Hours)
    A + B ; B+C ; C+A

    4th Criteria "No Shift" (Less than 5 Hrs Worked Hours)

    Whoever worked lesser than 5 hrs and Missed either In punch or Out punch would be 'No Shift'

    5th Criteria Shifts (8 to 8)

    In punch time - 8.00 AM and Out punch time - 20.00 / 22.00




    Excel Table

    ShiftIn PunchOut PunchWorked Hours
    NoShift08:34:0017:40:009
    B13:58:0022:00:008
    NoShift08:14:0017:32:009
    NoShift08:24:0017:33:008
    NoShift08:19:0013:00:005
    NoShift08:24:0017:32:009
    NoShift13:00:0013:38:0016
    NoShift08:36:0017:32:008
    NoShift06:34:0016:02:009
    NoShift07:03:0016:03:009
    NoShift06:34:0016:00:009
    NoShift06:48:0016:00:009
    NoShift21:47:0006:01:008
    C08:17:0017:30:008
    C21:37:00
    NoShift21:37:0006:02:008
    C08:12:0017:29:008
    C08:23:0017:36:008
    C08:21:0017:30:008
    C08:29:0017:34:008
    C08:27:0017:32:008
    C08:24:0017:34:008

    Thank you in advance.

    Attached Files:

    • dpt.xls
      File size:
      170.5 KB
      Views:
      10
  7. vletm

    vletm Excel Ninja

    Messages:
    4,166
    sarisri.
    You missed CLEAR sample result of those five criterias
    (including from which row those will come from)
    and
    because there are no connection of 'user's then what?
  8. sarisri

    sarisri New Member

    Messages:
    16
    As I understand the above question that you asked.

    Those criteria mentioned above (5) are based on 2 columns (In Punch & Worked Hours) shown in the earlier attached xl.

    Let me give you the process in detail;
    For instance, if I fill want to fill the in Shift as 'A', I would look at first "In punch' column tinning (if it is before 6.00AM/ after 6.00 AM), then I further look at 'Worked Hours' column (if it 8 -10 hrs). Then I would come to conclusion as the shift is 'A'.

    If the above 'Worked Hours' column is less than 5 Hours, I fill the Shift Cell as "No Shift'

    If the above 'Worked Hours' column is in between 11 to 15 Hours, I fill the Shift Cell as 'A + Extra Hours'

    If the above 'Worked Hours' column is above or equal 16 Hours, I fill the Shift Cell as 'A + B'

    It goes like that as per the earlier post mentioned about Criteria.

    Hope I am clear. Pl ask me any further clarifications.
  9. vletm

    vletm Excel Ninja

    Messages:
    4,166
    You missed CLEAR sample result of those five criterias
    (including from which row those will come from)
    and
    because there are no connection of 'user's then what?

    You missed from Your answer:
    THE FILE or SHEET which has sample results from Your dpt.xls.
    also
    'which row those will come from'.
  10. sarisri

    sarisri New Member

    Messages:
    16
    I am extremely sorry for giving you trouble, I am not able to figure out the question that you are asking. And guessing the below answer that you seek from me.

    Attached above dpt.xl file with 2 Sheets, Sheet 1 - Empty Shift column ; Sheet 2 - Sample format of the report with Shift.

    Is that you are looking for, if not kindly post me more collaboratively.

    Thanks in advance.

    Attached Files:

    • dpt.xls
      File size:
      163.5 KB
      Views:
      3
  11. vletm

    vletm Excel Ninja

    Messages:
    4,166
    sarisri
    How?
    You have offered for me:
    Shift (all empty), InPunch, OutPunch and
    WorkedHours (which could come from OutPunch-InPunch)...
    and
    now,
    You show from those information
    Your needed 'Sample Report' ... 26 columns ... WOW!

    Could You try to write step by step how did You do that from those 2-3 columns?
  12. sarisri

    sarisri New Member

    Messages:
    16
    I have give step by step procedure clearly in the attached.

    Pl look at the steps, hope you get more clarity that what I am looking at.

    Pl let me know any further info to share.

    Thank you in advance.

    Attached Files:

  13. vletm

    vletm Excel Ninja

    Messages:
    4,166
    sarisri
    Yes - step-by-step
    Have You tried to do those manually?
    I added few questions with my sample.
    BTW my yesterday reply stay here ...

    Attached Files:

  14. sarisri

    sarisri New Member

    Messages:
    16
    Attached file with my answers.

    I saw the results of worked hours and shifts, its great to see row by row filling the data. I will confirm it soon the Shifts and its suitability by doing it manually.

    Thank you for sparing your time.

    Attached Files:

  15. vletm

    vletm Excel Ninja

    Messages:
    4,166
    sarisri
    Many answers,
    but some answers cannot be correct
    or I cannot figure what did You try to answer.

    Attached Files:

  16. sarisri

    sarisri New Member

    Messages:
    16
    Attached xl file with my answers.

    Pls ask me for any further queries.

    Thank you.

    Attached Files:

  17. sarisri

    sarisri New Member

    Messages:
    16
    Hope my clarifications have met your queries.

    Pls ask me if any further to clarify.

    Thank you.
  18. vletm

    vletm Excel Ninja

    Messages:
    4,166
    sarisri
    Term Worked hours:
    If has worked between 5hrs and 8hrs then which RULE?
    If has worked 15hrs 25minutes then which RULE?
    Some RULEs says:
    #6 Reply: (less 5) (8-10) (11-15) (above 16)
    #8 Reply: (less 5) (8-10) (11-15) (above or equal 16)
    5hrs is more that (less 5) and less that 8!
    15:25 is more than 15hrs and less than 16hrs!
    ... or is 15:25 as 15hrs? It cannot be 16hrs (not full)
    I've to solve those working hours myself!
    DO NOT MESS with InPunch or OutPunch Times!

    What would 'rule' L6 mean? Name few from Your data!
    You didn't name from Your data! You wrote own sample!

    If You have many 'same rules' which has many different meaning.
    It's challenge to calculate!

    If You write 'something'
    which cannot be answer to my question,
    it won't help at all.
  19. sarisri

    sarisri New Member

    Messages:
    16
    I am extremely sorry for the inconvenience caused you during my conversation.

    After seeing your questions, above, I felt that I have been doing many mistakes in entering the Shifts Manually.

    I will come out soon with pure examples to all your queries.
  20. sarisri

    sarisri New Member

    Messages:
    16
    I have attached xl workbook having two sheets. 1). 'Fresh Rules' sheet 2). Old sheet that you worked.

    Pl look at 'Fresh Rules' Sheet in the attached to have full clarity on Shift rules, Worked hours rules and Standard timings.

    And also the Sheet, 'Fresh Rules' contains some Comment boxes to give you complete information of the Shift which supposed to be filled with.

    Thank you.

    Attached Files:

  21. vletm

    vletm Excel Ninja

    Messages:
    4,166
    sarisri
    I added few questions.
    Why there should be 'Old sheet' if that has ... rules?
    Or something which has come as results with ... rules?
    soon ... about 47hrs?
    I'll try to check after clear rules.

    Attached Files:

  22. sarisri

    sarisri New Member

    Messages:
    16
    I added the old sheet for your reference, which may not be much important to you. You may ignore it.

    Frankly, the questions asked in the sheet was unable to figure out.

    Could you send me collaboratively, pls.

    Thank you.
  23. vletm

    vletm Excel Ninja

    Messages:
    4,166
    sarisri
    This would have been ready few days ago if You would 'think' one second!
    Why someone would thinking these AFTERWARDs?Why ... it waits the reason for answer
    someone ... person
    would thinking ... something which comes in mind
    these ... column-A
    afterwards ... after those punches has done
    ? ... ends this question.
  24. sarisri

    sarisri New Member

    Messages:
    16
    My apologies for made you waiting, in fact, i was also confused with the timings of the shift initially and as you rightly pointed out in the earlier posts, I must be thankful to you. Finally, your questions really made me realize the mistake that i was doing so far when filling shifts manually.

    I found your VBA code perfectly working well with A+B, B+C, Extra Hours, No shifts etc.,

    But the problem is only with A, B, G shifts, which are not identified (of course my mistake also there for not giving clear cut shift timings in the beginning). The previous post with xl attachment is now filled the gap by giving more clarity of various shifts including Late Shifts.


    Thank you.
  25. vletm

    vletm Excel Ninja

    Messages:
    4,166
    sarisri
    Your #22 Reply -- ?
    My #23 Reply -- No answer?
    There won't use P-word! maybe challenge!
    There are still HOLES!
    eg if something would be <6 or >6 then there are HOLE!
    if 6 occurs then there are NO RULE!

Share This Page