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

[sarisri]

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

 

[vletm]

sarisri
Something like this...
but not always to NEW SHEET!

 

[sarisri]

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.

 

[vletm]

sarisri
Next something like this ...
it would include something that You would like to get.

 

[sarisri]

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.

 

[sarisri]

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.

 

[vletm]

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?

 

[sarisri]

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.

 

[vletm]

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'.

 

[sarisri]

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.

 

[vletm]

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?

 

[sarisri]

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.

 

[vletm]

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 ...

 

[sarisri]

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.

 

[vletm]

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

 

[sarisri]

Attached xl file with my answers.

Pls ask me for any further queries.

Thank you.

 

[sarisri]

Hope my clarifications have met your queries.

Pls ask me if any further to clarify.

Thank you.

 

[vletm]

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.

 

[sarisri]

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.

 

[sarisri]

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.

 

[vletm]

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.

 

[sarisri]

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.

 

[vletm]

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.

 

[sarisri]

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.

 

[vletm]

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!