Copying rows into different 2 different spreadsheets based on 2 colums

[N1x0N92]

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...…

Date Description Cost Centre Amount Reconciled 01/04/2019 Money in 1 Donations 100 Apr-19 02/04/2019 Money out 2 Donations -100 May-19 03/04/2019 Money in 2 Friends of School 50 Jun-19 04/04/2019 Money out 3 School Trips -50 Jul-19 05/04/2019 Money in 3 School Trips 70 Aug-19 06/04/2019 Money out 4 School Trips -70 Sep-19 07/04/2019 Money in 4 Donations Income 40 Oct-19 08/04/2019 Money out 5 Donations Expenditure -40 Nov-19 09/04/2019 Money in 5 School Trips 95 Dec-19 10/04/2019 Money out 6 School Trips -95 Jan-20 11/04/2019 Money in 6 Donations 45 Feb-20 12/04/2019 Money out 7 Donations -45 Mar-20 13/04/2019 Money in 7 School Trips 20 14/04/2019 Money out 8 School Trips -20 15/04/2019 Money in 8 Donations 10 16/04/2019 Money out 9 Donations -10 <tbody> </tbody> <strike></strike>
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>	<strike></strike>

<tbody>
</tbody>

 

[My Aswer Is This]

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

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

 

[N1x0N92]

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

 

[My Aswer Is This]

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>}}​

 

[N1x0N92]

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

 

[My Aswer Is This]

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

 

[N1x0N92]

Hi

did like you said howver i now get the same error meesage but after clicking column 3 (c)

 

[My Aswer Is This]

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

 

[N1x0N92]

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

 

[My Aswer Is This]

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.