PDA

View Full Version : Copy row based on criteria to existing sheets & constant update



pixyrene
07-26-2017, 02:59 AM
Good day.


I have been struggling REALLY hard to figure this out and need help please!
We have a sheet where data is entered (Master) and I want the entire row to be copied to the existing sheet with the same name as a cell in the master sheet.
For example...
If I enter "Wings" in cells in column N, the entire row should be copied to the sheet called Wings. The other thing that I need is that this row being copied should update on the sheet if anything changes in the master sheet.


Is this even possible?
I am new to VBA, but used it in the past.

offthelip
07-26-2017, 05:18 AM
try this, it write links into the sheet in row one back to the selected row. You put this code in the on change suroutine of the Master sheet


Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column) = 14 Then
rowno = Target.Row
actshtname = ActiveSheet.Name
shtname = Target.Value
worksheetExists = Evaluate("ISREF('" & shtname & "'!A1)")
If worksheetExists Then
For i = 1 To 14
collet = Chr(64 + i)
formtxt = "=" & actshtname & "!" & collet & rowno
' MsgBox formtxt
Worksheets(shtname).Cells(1, i).Formula = formtxt


Next i
End If
End If


End Sub

pixyrene
07-26-2017, 06:09 AM
Thank you very much.
This did not help.
Nothing changes when information is added in the master sheet.

offthelip
07-26-2017, 07:43 AM
I don't think you can have put the code in the right place, try this spreadshet because values in "Wish" sheet definitely change when "Master" changes. Look at the temp sheet and see that it is blank then try writing "temp" to N2 on Master sheet and see what happens.