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]
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Try this:
When you double click on cell in column A of sheet named Master
This row will be copied to the proper sheets
You will have to have sheet names for the values you enter into Columns C and E of Master sheet.

Example if you have "David" in column C and Apr-19 in column E this row will be copied to sheets "David" and sheets named "Apr-19"

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab named Master
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  7/23/2019  7:32:10 AM  EDT
If Target.Column = 1 Then
Cancel = True
On Error GoTo M
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)
End If
Exit Sub
M:
MsgBox "You do not have a sheet by that name"
End Sub
 
Upvote 0
Hi

Thanks so much for replying.

unfortunatley you are helping a complete beginner.

i thought i followed the steps however nothing appears to be happening. could you split it down for me in baby steps?

Sorry and thanks in advance
 
Upvote 0
Did you read and understand how to install the script?
See I said this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab named Master
Select View Code from the pop-up context menu
Paste the code in the VBA edit window



Now when you double click on any cell in column A that row of data will be copied to the proper sheet.

And I said:

You will have to have sheet names for the values you enter into Columns C and E of Master sheet.​


Did you double click on column A ?
If yes then
What happened?

​
<strike>
</strike>​
 
Upvote 0
Hi

Yes however when i double click in column A it says 'You do not have a sheet by this name'.

which is true for column A but this is not the information i want it driven by. I need it to look a columns C & F.

Hope this makes sense.

Kind regards
 
Upvote 0
Not sure how this happened.
I test all my scripts but some how I messed up

Change this line of code:
If Target.Column = 3 Then

Change 1 to 3

Then double click on value in column 3
 
Last edited:
Upvote 0
That is maybe you do not a sheet by that name

If you have "Alpha" in column 3 you must have a sheet named "Alpha"

And you have Apr-17 in Column E you must have a sheet named Apr-17
 
Upvote 0
Hi

the above has now worked an is brilliantly.

my next thought is.....is there any way I can identify lines that have been 'double clicked' so they are not duplicated?

I know I can highlight duplicates within the other pages however it would be great if each line could be limited to been distributed once.

hope the above makes sense.

Kind regards
 
Upvote 0
Not sure what you want to happen if it is duplicated.
Do you want the one just double clicked on copied to other sheet and previous one copied to other sheet deleted

Or do you want the one double clicked on just do nothing if it has already been copied over.
 
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
77
kumara_faith
K
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
585
awoohaw
awoohaw
M
  • Solved
Returning results based on multiple criteria
Replies
5
Views
478
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
499
XLfriend777
XLfriend777
H
  • Question Question
copy some columns based on the value in column "A".
Replies
9
Views
342
Cubist
Cubist

Forum statistics

Threads
1,223,157
Messages
6,170,419
Members
452,325
Latest member
BlahQz

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