Data link to multiple pages

[Fixitphillips]

So I'll begin with, I'm a novice with excel. I understand basic sum formulas and if I am working within on sheet or linking information to another sheet I do ok.

However what I'm trying to research and learn how to do is to have a master sheet to input information, and based off of information from a drop-down box it determines which sheet it transfers the information to.

I do construction, and some days either myself or my wife are doing multiple purchases and I'm trying to accomplish a one stop data drop that automates cost accounting.

Not sure if this is a vba solution, or index/match or something else. Just hoping for a direction for research.

 

[My Aswer Is This]

Well I would use a Worksheet Double click script:
So lets say you have some data on sheet named master.
You enter some data in the row and then want that data copied to another sheet.
In say column G you have a drop down list with all your sheet names.
So if you double click on "Alpha" this row of data is copied to sheet named "Alpha"
Would something like that work?
It would require using Vba but I can send you the code for this to work.

 

[Fixitphillips]

I'm no sure if that would work or not as I'm still learning, but please send me the code. I learn from seeing and I find Excel coding enjoyable, and even if this is not exactly what I need this time the more I learn the better.

 

[My Aswer Is This]

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

Now in this script you need to have your sheet names in Column 4

When you double click on a sheet name in column 4 that row of data will be copied to that sheet name.
We assume you always have some data in column A of each row.

So double click on sheet name in column 4 of master sheet.
The sheet you put this code in. And the script will run.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  11/7/2021  11:45:32 PM  EST
Cancel = True
On Error GoTo M
If Target.Column = 4 Then
Dim Lastrow As Long
Dim sn As String
sn = Target.Value
Lastrow = Sheets(sn).Cells(Rows.Count, "A").End(xlUp).Row + 1
ans = Target.Row
Rows(ans).Copy Sheets(Target.Value).Cells(Lastrow, 1)
End If
Exit Sub
M:
MsgBox "We have no sheet named" & vbNewLine & Target.Value, , "Oops"
End Sub

 

[My Aswer Is This]

I made one small mistake: Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  11/8/2021  12:06:22 AM  EST
Cancel = True
On Error GoTo M
If Target.Column = 4 Then
Dim Lastrow As Long
Dim sn As String
Dim ans As long
sn = Target.Value
Lastrow = Sheets(sn).Cells(Rows.Count, "A").End(xlUp).Row + 1
ans = Target.Row
Rows(ans).Copy Sheets(Target.Value).Cells(Lastrow, 1)
End If
Exit Sub
M:
MsgBox "We have no sheet named" & vbNewLine & Target.Value, , "Oops"
End Sub