Sally mokhtar
New Member
- Joined
- Dec 8, 2020
- Messages
- 10
- Office Version
- 2016
- Platform
- Windows
hello Everyone,
Am a Junior HR.. who is actually swamped in dozens of excel files that I have to manage monthly.
I have a certain task that i would really appreciate if I can automate !!
what I do is as follows:
Each month I receive a sheet with all our employees money in and out amounts( Dr. and Cr. amounts), i copy that amounts from the employees sheet and paste it in the main sheet to be reviewed by my boss.
the employees sheet:
The main sheet:
what i do :
I have to copy the amounts in column B and C (DR. and Cr. amounts) from the first sheet mentioned (employees sheet) and paste in the main sheet (in the above pic)
Problem is:
when i copy the DR. and Cr. amount of an employee (for ex: Vesta) from employees sheet to paste in the Main sheet, i have to search within the tabs highlighted in the bottom of the excel sheet till i find that employee and paste the amounts in column B and C and so on.
Is there a Macro where i can copy the amounts of each employee from the first sheet and then go to the second sheet, search for that employee in the bottom tabs and paste those amounts automatically or I can't do that ?
Note: my boss doesn't want to change the main sheet so I've to stick with it.
if there is any way to automate it, that would literally save me 100 of hrs. copying and pasting 2400 employees data.
Thanks in advance.
Am a Junior HR.. who is actually swamped in dozens of excel files that I have to manage monthly.
I have a certain task that i would really appreciate if I can automate !!
what I do is as follows:
Each month I receive a sheet with all our employees money in and out amounts( Dr. and Cr. amounts), i copy that amounts from the employees sheet and paste it in the main sheet to be reviewed by my boss.
the employees sheet:
Employees.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | AMOUNT | Dr | Cr | TRADATE | |||
2 | 146.25 | 146.25 | 0.00 | 11/15/2020 | Vesta | ||
3 | 246.25 | 246.25 | 0.00 | 11/15/2020 | Cicely | ||
4 | 746.25 | 746.25 | 0.00 | 11/15/2020 | Brenna | ||
5 | -1,352.50 | 0.00 | -1,352.50 | 11/10/2020 | Shelli | ||
6 | 146.25 | 146.25 | 0.00 | 11/15/2020 | Q. | ||
7 | -162.75 | 0.00 | -162.75 | 11/10/2020 | Leona | ||
8 | 162.75 | 162.75 | 0.00 | 11/28/2020 | Clara | ||
9 | 44,000.00 | 44,000.00 | 0.00 | 11/01/2020 | Maren | ||
10 | -44,000.00 | 0.00 | -44,000.00 | 11/01/2020 | Honey | ||
11 | 196.25 | 196.25 | 0.00 | 11/15/2020 | Klavdia | ||
12 | 72.62 | 72.62 | 0.00 | 11/08/2020 | Rosina | ||
13 | 78,000.00 | 78,000.00 | 0.00 | 11/01/2020 | Libby | ||
14 | -78,000.00 | 0.00 | -78,000.00 | 11/01/2020 | Jerrie | ||
15 | 646.25 | 646.25 | 0.00 | 11/15/2020 | Magdalene | ||
16 | 5.54 | 5.54 | 0.00 | 11/10/2020 | Sammy-Jo | ||
17 | -7,791.89 | 0.00 | -7,791.89 | 11/08/2020 | DeeAnn | ||
18 | -546.25 | 0.00 | -546.25 | 11/10/2020 | Stone | ||
19 | 246.25 | 246.25 | 0.00 | 11/15/2020 | Lucille | ||
20 | 10,000.00 | 10,000.00 | 0.00 | 11/18/2020 | Edith | ||
21 | -626.25 | 0.00 | -626.25 | 11/10/2020 | Ena | ||
22 | -10,000.00 | 0.00 | -10,000.00 | 11/18/2020 | Adriènne | ||
23 | 526.25 | 526.25 | 0.00 | 11/26/2020 | Miles | ||
24 | -157.47 | 0.00 | -157.47 | 11/10/2020 | Evan | ||
25 | -202.00 | 0.00 | -202.00 | 11/30/2020 | Pete | ||
26 | 10,000.00 | 10,000.00 | 0.00 | 11/18/2020 | Brigham | ||
27 | -10,000.00 | 0.00 | -10,000.00 | 11/18/2020 | Roma | ||
28 | -342.50 | 0.00 | -342.50 | 11/10/2020 | Daniel | ||
29 | 63.24 | 63.24 | 0.00 | 11/25/2020 | Gabriele | ||
30 | -249,826.71 | 0.00 | -249,826.71 | 11/29/2020 | Jasper | ||
31 | 163.61 | 163.61 | 0.00 | 11/28/2020 | Vesta | ||
32 | -163.61 | 0.00 | -163.61 | 11/10/2020 | Cicely | ||
33 | 3,377.41 | 3,377.41 | 0.00 | 11/08/2020 | Brenna | ||
34 | -3,377.41 | 0.00 | -3,377.41 | 11/08/2020 | Shelli | ||
35 | -8,606.02 | 0.00 | -8,606.02 | 11/08/2020 | Q. | ||
36 | -646.25 | 0.00 | -646.25 | 11/10/2020 | Leona | ||
37 | -162.75 | 0.00 | -162.75 | 11/10/2020 | Clara | ||
38 | 162.75 | 162.75 | 0.00 | 11/28/2020 | Maren | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2:B38 | B2 | =IF(A2>0,A2,0) |
C2:C38 | C2 | =IF(A2<0,A2,0) |
The main sheet:
what i do :
I have to copy the amounts in column B and C (DR. and Cr. amounts) from the first sheet mentioned (employees sheet) and paste in the main sheet (in the above pic)
Problem is:
when i copy the DR. and Cr. amount of an employee (for ex: Vesta) from employees sheet to paste in the Main sheet, i have to search within the tabs highlighted in the bottom of the excel sheet till i find that employee and paste the amounts in column B and C and so on.
Is there a Macro where i can copy the amounts of each employee from the first sheet and then go to the second sheet, search for that employee in the bottom tabs and paste those amounts automatically or I can't do that ?
Note: my boss doesn't want to change the main sheet so I've to stick with it.
if there is any way to automate it, that would literally save me 100 of hrs. copying and pasting 2400 employees data.
Thanks in advance.