Copying rows into different 2 different spreadsheets based on 2 colums

N

N1x0N92

New Member
Joined
Jul 23, 2019
Messages
7
Hi

First post so hopefully I can give enough information for someone to help me :)

Im trying to set up a spreadsheet that will allow a colleague to enter information on the MASTER that will then feed into other sheets.

The below a copy from the master sheet...…

[TABLE="width: 520"]
<tbody>[TR]
[TD][TABLE="width: 520"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Description[/TD]
[TD]Cost Centre[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD]Reconciled[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/04/2019[/TD]
[TD]Money in 1[/TD]
[TD]Donations[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD="align: right"]Apr-19[/TD]
[/TR]
[TR]
[TD="align: right"]02/04/2019[/TD]
[TD]Money out 2[/TD]
[TD]Donations[/TD]
[TD="align: right"]-100[/TD]
[TD][/TD]
[TD="align: right"]May-19[/TD]
[/TR]
[TR]
[TD="align: right"]03/04/2019[/TD]
[TD]Money in 2[/TD]
[TD]Friends of School[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD="align: right"]Jun-19[/TD]
[/TR]
[TR]
[TD="align: right"]04/04/2019[/TD]
[TD]Money out 3[/TD]
[TD]School Trips[/TD]
[TD="align: right"]-50[/TD]
[TD][/TD]
[TD="align: right"]Jul-19[/TD]
[/TR]
[TR]
[TD="align: right"]05/04/2019[/TD]
[TD]Money in 3[/TD]
[TD]School Trips[/TD]
[TD="align: right"]70[/TD]
[TD][/TD]
[TD="align: right"]Aug-19[/TD]
[/TR]
[TR]
[TD="align: right"]06/04/2019[/TD]
[TD]Money out 4[/TD]
[TD]School Trips[/TD]
[TD="align: right"]-70[/TD]
[TD][/TD]
[TD="align: right"]Sep-19[/TD]
[/TR]
[TR]
[TD="align: right"]07/04/2019[/TD]
[TD]Money in 4[/TD]
[TD]Donations Income[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD="align: right"]Oct-19[/TD]
[/TR]
[TR]
[TD="align: right"]08/04/2019[/TD]
[TD]Money out 5[/TD]
[TD]Donations Expenditure[/TD]
[TD="align: right"]-40[/TD]
[TD][/TD]
[TD="align: right"]Nov-19[/TD]
[/TR]
[TR]
[TD="align: right"]09/04/2019[/TD]
[TD]Money in 5[/TD]
[TD]School Trips[/TD]
[TD="align: right"]95[/TD]
[TD][/TD]
[TD="align: right"]Dec-19[/TD]
[/TR]
[TR]
[TD="align: right"]10/04/2019[/TD]
[TD]Money out 6[/TD]
[TD]School Trips[/TD]
[TD="align: right"]-95[/TD]
[TD][/TD]
[TD="align: right"]Jan-20[/TD]
[/TR]
[TR]
[TD="align: right"]11/04/2019[/TD]
[TD]Money in 6[/TD]
[TD]Donations[/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD="align: right"]Feb-20[/TD]
[/TR]
[TR]
[TD="align: right"]12/04/2019[/TD]
[TD]Money out 7[/TD]
[TD]Donations[/TD]
[TD="align: right"]-45[/TD]
[TD][/TD]
[TD="align: right"]Mar-20[/TD]
[/TR]
[TR]
[TD="align: right"]13/04/2019[/TD]
[TD]Money in 7[/TD]
[TD]School Trips[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14/04/2019[/TD]
[TD]Money out 8[/TD]
[TD]School Trips[/TD]
[TD="align: right"]-20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15/04/2019[/TD]
[TD]Money in 8[/TD]
[TD]Donations[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2019[/TD]
[TD]Money out 9[/TD]
[TD]Donations[/TD]
[TD="align: right"]-10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
I then have a sheet for each Cost Centre and a sheet for reconciled for example (Donations and April 19)

what I then need to happen is the whole row to be copied into the relevant cost centre and also the reconciled month.

is this possible?

Any help would be fantastic.

Kind regards ​
<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
hi

"""Or do you want the one double clicked on just do nothing if it has already been copied over. """

the above would be perfect

kind regards
​
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Try this:
When you double click on column 3
That cell will be colored Red and the script continues.
If that cell is already colored Red the script will tell you that row has already been copied over and the script will stop and do nothing.

Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  8/8/2019  3:41:50 AM  EDT
If Target.Column = 3 Then
Cancel = True
On Error GoTo M
If Target.Interior.Color = vbRed Then MsgBox "That row has already been copied" & vbNewLine & "I will stop this script": Exit Sub
Dim Lastrowc As Long
Dim ans As String
Dim Lastrowe As Long
ans = Format(Cells(Target.Row, "E"), "MMM-YY")
Lastrowc = Sheets(Cells(Target.Row, "C").Value).Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowe = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(Target.Row).Copy Sheets(Cells(Target.Row, "C").Value).Rows(Lastrowc)
Rows(Target.Row).Copy Sheets(ans).Rows(Lastrowe)
Target.Interior.Color = vbRed
End If
Exit Sub
M:
MsgBox "You do not have a sheet by that name"
End Sub
 
Upvote 0
Hi

this is brilliant!

i have another request, can the script stope copying after column F.

so when it copies the date into the new sheets it copies colums A to F but then leaves the data i have manaully keyed in the new spreadsheets after that?

Thanks in advance.
 
Upvote 0
Try this:
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  8/14/2019  1:09:20 PM  EDT
If Target.Column = 3 Then
Cancel = True
On Error GoTo M
If Target.Interior.Color = vbRed Then MsgBox "That row has already been copied" & vbNewLine & "I will stop this script": Exit Sub
Dim Lastrowc As Long
Dim ans As String
Dim Lastrowe As Long
ans = Format(Cells(Target.Row, "E"), "MMM-YY")
Lastrowc = Sheets(Cells(Target.Row, "C").Value).Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowe = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(Target.Row, 1).Resize(, 6).Copy Sheets(Cells(Target.Row, "C").Value).Cells(Lastrowc, 1)
Cells(Target.Row, 1).Resize(, 6).Copy Sheets(ans).Cells(Lastrowe, 1)
Target.Interior.Color = vbRed
End If
Exit Sub
M:
MsgBox "You do not have a sheet by that name"
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Populate Based on Actual and Budget
Replies
2
Views
88
kumara_faith
K
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
600
awoohaw
awoohaw
M
  • Solved
Returning results based on multiple criteria
Replies
5
Views
497
DanteAmor
DanteAmor
K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
514
XLfriend777
XLfriend777
H
  • Question Question
copy some columns based on the value in column "A".
Replies
9
Views
360
Cubist
Cubist

Forum statistics

Threads
1,223,886
Messages
6,175,194
Members
452,616
Latest member
intern444

Share this page

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
Back
Top