Moving data from one sheet to another

[Sally mokhtar]

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:

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.

 

[mohadin]

Hi
Both excel file are open (Employee & MAINSHEET)
and place the code in a module in the Employee file, OK?
Then run the code

Sub test()
   Application.ScreenUpdating = False
    a = Cells(2, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1, 5)
    Windows("MAINSHEET.xlsx").Activate
    For i = 1 To UBound(a)
        On Error Resume Next
        With Sheets(a(i, 5))
            lr = .Cells(Rows.Count, 2).End(xlUp).Row
            .Cells(lr + 1, 2) = a(i, 2)
            .Cells(lr + 1, 3) = a(i, 3)
        End With
    Next
   Application.ScreenUpdating = True
End Sub

 

[Sally mokhtar]

Working perfectly, Mohadin !!!!

only one thing: i want the amount (163.61) to be after the last amount here 8,600 not at the top of the sheet
how can i fix that?

Hi
Both excel file are open (Employee & MAINSHEET)
and place the code in a module in the Employee file, OK?
Then run the code

Sub test()
   Application.ScreenUpdating = False
    a = Cells(2, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1, 5)
    Windows("MAINSHEET.xlsx").Activate
    For i = 1 To UBound(a)
        On Error Resume Next
        With Sheets(a(i, 5))
            lr = .Cells(Rows.Count, 2).End(xlUp).Row
            .Cells(lr + 1, 2) = a(i, 2)
            .Cells(lr + 1, 3) = a(i, 3)
        End With
    Next
   Application.ScreenUpdating = True
End Sub

 

[mohadin]

Yes
The Problem I can not see where the DEbet.... located in the sheet from your Pic.

 

[Sally mokhtar]

oh sorry about that

HYG

Others 1619208-AEA-Oct-2020.xls
	A	B	C	D
1		163.61	-
2
3
4
5	===================	====================	====================
6	DEBIT	CREDIT	BALANCE
7	===================	====================	====================
8			-
9	13,000.00		13,000.00
10		13,000.00	-
11	13,750.00		13,750.00
12	13,750.00		27,500.00
13	13,750.00		41,250.00
14	13,720.00		54,970.00
15	13,000.00		67,970.00
16		13,750.00	54,220.00
17		13,750.00	40,470.00
18		13,750.00	26,720.00
19		13,720.00	13,000.00
20		13,000.00	-
21	13,000.00		13,000.00
22		13,000.00	-
23	13,107.00		13,107.00
24		13,107.00	-
25	8,600.00		8,600.00
26		8,600.00	-
27
28
29
30
31
32
33
34
Vesta

Cell Formulas
Range	Formula
C8	C8	=A8-B8
C9:C26	C9	=C8+A9-B9

 

[mohadin]

Try

Sub test()
    Application.ScreenUpdating = False
    a = Cells(2, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1, 5)
    Windows("MAINSHEET.xlsx").Activate
    For i = 1 To UBound(a)
        On Error Resume Next
        With Sheets(a(i, 5))
            .Cells(8, 1) = "DEBIT": .Cells(8, 2) = "CREDIT": Cells(8, 3) = "BALANCE"
            lr = .Cells(Rows.Count, 2).End(xlUp).Row
            .Cells(lr + 1, 2) = a(i, 2)
            .Cells(lr + 1, 3) = a(i, 3)
        End With
    Next
    Application.ScreenUpdating = True
End Sub

 

[Sally mokhtar]

same thing.

the numbers appear on B1 and C1

 

[Sally mokhtar]

Any ideas ?

 

[mohadin]

Sorry for being late,
I did a lot of test every thing is OK in here
unless there is something in your MAIN file

 

[mohadin]

Hi Sally
have you replace the code with the one in #6