I have the Main sheet where I want to input any new orders and I have other sheets where the orders get filtered down depending on one criterion.
The other sheet records the orders and keeps track of how much supply I have left. So it is essential that I can copy over at least the number that has been used up in the other sheets.
I have been unable to find an automated way of doing this and have been trying to tackle this for a while now. I only want one column copied over (to make everything simpler, but wouldn't mind the whole row if that is possible).
I have attached an example excel file. Any help would be greatly appreciated.
I think you need to rephrase your requirement as it is not clear what you need to copy over to other sheets and where exactly.
Why not explain the logic taking one row as an example?
e.g. lets assume you enter data in row#32 as per the existing data in your sample file, what do you want to do next? Please explain all the manual steps which you would take to achieve the desired output which would be copying some data from Main sheet to the Kiera Pewter Sheet and the question here is what to copy from Main Sheet and paste where on Kiera Pewter Sheet?
From the main sheet, I want the cell F32 to go into cell B11 on the sheet called 'Kiera Pewter'. I also want excel to choose which sheet to copy the cell in based on what it finds in the cell D32. Eg. it said 'Kiera Pewter' so it should choose the sheet called 'Kiera Pewter' and then copy it in there. If it was that it said 'Kiera Cream' as it does in D31, I want it to copy the 'Total Fabric required' value into B11 in the sheet titled 'Kiera Cream'.
Please find the attached with a Sheet Change Event code on the Main Sheet Module.
To view the code, right click on Main Sheet Tab --> View code.
As per the code once you input a value in any row in column E, the code will first check if the columns A:D for that row are filled, if so, the code will copy the value from column F from that row to the sheet which is mentioned in column D.
Here is the Change Event Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim ws As Worksheet
Dim cntFilled As Long
Dim r As Long
If Target.Column = 5 And Target.Row > 3 Then
If Target <> "" Then
Application.EnableEvents = False
r = Target.Row
cntFilled = Application.CountA(Range("A" & r & ":D" & r))
If cntFilled <> 4 Then
MsgBox "Please fill all the cells in the range " & Range("A" & r & ":D" & r).Address(0, 0) & " first and then try again...", vbExclamation
On Error Resume Next
Set ws = Worksheets(Range("D" & r).Value)
On Error GoTo 0
If ws Is Nothing Then
MsgBox "The Sheet called '" & Range("D" & r).Value & "' doesn't exists in this Workbook." & _
" Please insert a Sheet First and then try again...", vbExclamation
ws.Range("B" & Rows.Count).End(3)(2).Value = Range("F" & r).Value
Range("G" & r).Value = "Transferred"
Application.EnableEvents = True
Let me know if that is what you were trying to achieve.
If that takes care of your original question, please accept this post as an Answer to mark your question as Solved and give a like to the post if you wish.
Best Response confirmed by
saramabbas (New Contributor)