09-09-2019 08:30 AM
09-09-2019 08:30 AM
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.
Thanks in Advance.
09-09-2019 08:54 AM
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?
09-09-2019 09:29 AM
@Subodh_Tiwari_sktneer Thanks for taking the time to read all that.
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'.
I hope that answers your question. Thanks again.
09-09-2019 10:03 AMSolution
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 Application.Undo GoTo Skip End If 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 GoTo Skip End If ws.Range("B" & Rows.Count).End(3)(2).Value = Range("F" & r).Value Range("G" & r).Value = "Transferred" End If End If Skip: Application.EnableEvents = True End Sub
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.