SOLVED

I want to enter data in one sheet and have it be copied to other sheets based on a column

Copper Contributor

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.

3 Replies

@saramabbas 

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?

 

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

best response confirmed by saramabbas (Copper Contributor)
Solution

@saramabbas 

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.

 

 

 

1 best response

Accepted Solutions
best response confirmed by saramabbas (Copper Contributor)
Solution

@saramabbas 

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.

 

 

 

View solution in original post