Moving data from one sheet to another

Sally mokhtar

New Member
Joined
Dec 8, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. 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:

Employees.xlsx
ABCDE
1AMOUNTDrCrTRADATE
2146.25146.250.00 11/15/2020Vesta
3246.25246.250.00 11/15/2020Cicely
4746.25746.250.00 11/15/2020Brenna
5-1,352.500.00-1,352.50 11/10/2020Shelli
6146.25146.250.00 11/15/2020Q.
7-162.750.00-162.75 11/10/2020Leona
8162.75162.750.00 11/28/2020Clara
944,000.0044,000.000.00 11/01/2020Maren
10-44,000.000.00-44,000.00 11/01/2020Honey
11196.25196.250.00 11/15/2020Klavdia
1272.6272.620.00 11/08/2020Rosina
1378,000.0078,000.000.00 11/01/2020Libby
14-78,000.000.00-78,000.00 11/01/2020Jerrie
15646.25646.250.00 11/15/2020Magdalene
165.545.540.00 11/10/2020Sammy-Jo
17-7,791.890.00-7,791.89 11/08/2020DeeAnn
18-546.250.00-546.25 11/10/2020Stone
19246.25246.250.00 11/15/2020Lucille
2010,000.0010,000.000.00 11/18/2020Edith
21-626.250.00-626.25 11/10/2020Ena
22-10,000.000.00-10,000.00 11/18/2020Adriènne
23526.25526.250.00 11/26/2020Miles
24-157.470.00-157.47 11/10/2020Evan
25-202.000.00-202.00 11/30/2020Pete
2610,000.0010,000.000.00 11/18/2020Brigham
27-10,000.000.00-10,000.00 11/18/2020Roma
28-342.500.00-342.50 11/10/2020Daniel
2963.2463.240.00 11/25/2020Gabriele
30-249,826.710.00-249,826.71 11/29/2020Jasper
31163.61163.610.00 11/28/2020Vesta
32-163.610.00-163.61 11/10/2020Cicely
333,377.413,377.410.00 11/08/2020Brenna
34-3,377.410.00-3,377.41 11/08/2020Shelli
35-8,606.020.00-8,606.02 11/08/2020Q.
36-646.250.00-646.25 11/10/2020Leona
37-162.750.00-162.75 11/10/2020Clara
38162.75162.750.00 11/28/2020Maren
Sheet1
Cell Formulas
RangeFormula
B2:B38B2=IF(A2>0,A2,0)
C2:C38C2=IF(A2<0,A2,0)


The main sheet:

1607510003276.png



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.
 

Sally mokhtar

New Member
Joined
Dec 8, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Hi Sally
have you replace the code with the one in #6
yes i did
VBA Code:
Sub test()
    a = Cells(2, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1, 5)
    Windows("Others 1619208-AEA-Oct-2020.xls").Activate
    For i = 2 To UBound(a)
        On Error Resume Next
        With Sheets(a(i, 5))
            .Cells(6, 1) = "DEBIT": .Cells(6, 2) = "CREDIT": Cells(6, 3) = "BALANCE"
            lr = .Cells(Rows.Count, 1).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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Sally mokhtar

New Member
Joined
Dec 8, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
1607600731360.png

instead of 8, I put in 5 because when I left it as 8, it would make the entire Data in column C go #Value as you can see in the excel attached below, also the macro adds the word debit, credit by it self as you can see in cell A8 and B8
anyways, the totals still appear in B1 and C1 either way.

Others 1619208-AEA-Oct-2020.xls
ABCD
1 163.61 -
2
3
4
5 =================== ==================== ====================
6 DEBIT CREDIT BALANCE
7 =================== ==================== ====================
8DEBIT CREDIT #VALUE!
913,000.00#VALUE!
1013,000.00#VALUE!
1113,750.00#VALUE!
1213,750.00#VALUE!
1313,750.00#VALUE!
1413,720.00#VALUE!
1513,000.00#VALUE!
1613,750.00#VALUE!
1713,750.00#VALUE!
1813,750.00#VALUE!
1913,720.00#VALUE!
2013,000.00#VALUE!
2113,000.00#VALUE!
2213,000.00#VALUE!
2313,107.00#VALUE!
2413,107.00#VALUE!
258,600.00#VALUE!
268,600.00#VALUE!
27
28
29
30
Vesta
Cell Formulas
RangeFormula
C8C8=A8-B8
C9:C26C9=C8+A9-B9
 

Attachments

  • 1607600707131.png
    1607600707131.png
    18.5 KB · Views: 0

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Hi Sally
Would you please try This 2 files

Let me Know if any problem
Thank you
 

Sally mokhtar

New Member
Joined
Dec 8, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
i copied the code and now it's working just fine YAAAAAY , i really don't know how to thank you :love: !!
you saved me literally 100 of hrs, thank you so much Mohadin !
Hi Sally
Would you please try This 2 files

Let me Know if any problem
Thank you
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Great
So happy I could Help You
And thank you for the feedback
Be happy
 

Sally mokhtar

New Member
Joined
Dec 8, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Great
So happy I could Help You
And thank you for the feedback
Be happy
a = .Cells(2, 1).Resize(Cells(Rows.Count, 5).End(xlUp).Row - 1, 5)

Would you please be so kind to explain this line of code to me, thanks alot.
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Well,
.Cells(2,1) (the cell in row 2, column 1) this refers to Range("A2")
And the period related to the line code before "with Activesheets" so
.Cellls(2,1)= Activesheets.Cells(2,1)=Activesheets.Range("A1")
Now,
.Cells(2,1).Resize(rows,columns), this to expand the range from cells(2,1) for number of rows and number of columns
Examples

VBA Code:
Sub sally1()
    With activesheets
        .Cells(2, 1).Select
    End With
End Sub
Sub sally2()
    With activesheets
        .Cells(2, 1).Resize(15, 5).Select
    End With
End Sub
Then
VBA Code:
Cells(Rows.Count, 5).End(xlUp).Row
To calculate the number of rows (witch we don't know ) in your target sheet It is normally vary from month to month
So starting from cells(2,1) or Range("A2") witch is just next to the first header down numbers of rows and 5 column (columns are fixed every month I guess)
After that process allocate all this to an array called <<a>> for further process in the next part of the given code.

I hope I explained this in a good manner.
I know I'm a bad teacher.
Any way do not hesitate to ask, I will do my best
Thank you.
My best regards
 

Sally mokhtar

New Member
Joined
Dec 8, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Well,
.Cells(2,1) (the cell in row 2, column 1) this refers to Range("A2")
And the period related to the line code before "with Activesheets" so
.Cellls(2,1)= Activesheets.Cells(2,1)=Activesheets.Range("A1")
Now,
.Cells(2,1).Resize(rows,columns), this to expand the range from cells(2,1) for number of rows and number of columns
Examples

VBA Code:
Sub sally1()
    With activesheets
        .Cells(2, 1).Select
    End With
End Sub
Sub sally2()
    With activesheets
        .Cells(2, 1).Resize(15, 5).Select
    End With
End Sub
Then
VBA Code:
Cells(Rows.Count, 5).End(xlUp).Row
To calculate the number of rows (witch we don't know ) in your target sheet It is normally vary from month to month
So starting from cells(2,1) or Range("A2") witch is just next to the first header down numbers of rows and 5 column (columns are fixed every month I guess)
After that process allocate all this to an array called <<a>> for further process in the next part of the given code.

I hope I explained this in a good manner.
I know I'm a bad teacher.
Any way do not hesitate to ask, I will do my best
Thank you.
My best regards
that was more than enough, it was really simple and now i understand everything you did.
again, am really so thankful for your time and effort.. you literally made me a huge favor and will save me alot of time!!

Thanks Mohadin :,)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,679
Members
415,921
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top