Copy from one sheet to another sheet depend on cell value

[KlausW]

Hi all Excel helpers.
I have a challenge.
In a sheet, I test whether my employees have received the uniform parts they need, if I put a number next to the uniform parts in question and the employee in question, they have not received what they need.
In column B, the uniform parts are cell B2 to cell B122. In rows C1 to V1, employee no.
Now it's like that I would like to have moved employee number and the uniform parts with number over to a new sheet. It has to be formal.
All help will be appreciated
With best regards
Klaus W

 

[Saurabhj]

Hi KlausW,

Please share the excel data to understand the requirement better.

Thanks,
Saurabh

 

[KlausW]

Hi Saurabhj

When I enter in cell C3 number how many uniform parts employees 6301 are missing. Should what is in cell A3 Material no.8465999572838 is transferred to another sheet “Tøj mangler”, and when I type in cell D4, what is in cell A4 Material no. 8465997252005 must be transferred to the same sheets “Tøj mangler”. But only where something stands out. Employees 6303 should not be transferred to the Sheet “Tøj mangler”. See picture 1.

The result should look like picture 2.

Yours sincerely Klaus W

 

[Saurabhj]

Hi,

Please check attached file for the code.

Code to Copy Paste data

Saurabh

 

[Fluff]

@Saurabhj please post the code to the site, as per Rule#4. Thanks

 

[Saurabhj]

Hi KlausW,

Use below code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastCol As Integer, colno As Integer, lastRow As Integer
    Dim colExists As String, employee As Integer
    colExists = "N"
    lastCol = Sheets("TOJ MANGLER").Cells(1, Columns.Count).End(xlToLeft).Column
    If Target.Column > 2 Then
        If Target.Value <> "" Then
            employee = Sheets("Material").Cells(1, Target.Column)
            For colno = 1 To lastCol
                If Sheets("TOJ MANGLER").Cells(1, colno) = employee Then
                    colExists = "Y"
                    lastRow = Sheets("TOJ MANGLER").Cells(Rows.Count, colno).End(xlUp).Row
                    Sheets("Material").Range("A" & Target.Row & ":B" & Target.Row).Copy Sheets("TOJ MANGLER").Cells(lastRow + 1, colno)
                    Sheets("TOJ MANGLER").Cells(lastRow + 1, colno + 2) = Target.Value
                End If
            Next
            If colExists = "N" Then
                Sheets("TOJ MANGLER").Cells(1, lastCol + 3) = employee
                Sheets("Material").Range("A" & Target.Row & ":B" & Target.Row).Copy Sheets("TOJ MANGLER").Cells(2, lastCol + 3)
                Sheets("TOJ MANGLER").Cells(2, lastCol + 5) = Target.Value
            End If
           
        End If
    End If
End Sub

I have used sheet as below:

CopyPasteData-Condition.xlsm
	A	B	C	D	E	F	G	H	I
1	Material No	Material	6301	6302	6303	6304	6305	6306	6307
2	22657615	A
3	33456945	B	1
4	30287017	C
5	38787886	D
6	46476555	E
7	46356993	F
8	21836799	G			4
9	46587808	H
10	43076941	I
11	36356316	J
12	25867822	K
13	43266937	L
14	32027954	M
15	20126099	N
16	47517530	O		1
17	46727837	P		1
18	32006883	Q
19	27646617	R
20	40286934	S
21	49486437	T
22	41306226	U
23	34317743	V
24	41646165	W
25	21997425	X
26	23107349	Y
27	33656833	Z
28	47576723	AA
Material

 

[KlausW]

Dear

Saurabhj it lookes very nice, I have a some questions but they will come tomorrow as it is evening here in Denmark. Klaus W​

 

[KlausW]

Dear Saurabh If I put the code into my own sheet. And deletes everything in sheets Material, and insert numbers again, insert the numbers in column D instead of in column A of the TOJ MISSING sheet. Sincerely, Klaus W

 

[Saurabhj]

Hi, check below code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastCol As Integer, colno As Integer, lastRow As Integer
    Dim colExists As String, employee As Integer
    colExists = "N"
    lastCol = Sheets("TOJ MANGLER").Cells(1, Columns.Count).End(xlToLeft).Column
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column > 2 Then
        'MsgBox Target.Column
        If Target.Value <> "" Then
            'MsgBox Target.Value
            employee = Sheets("Material").Cells(1, Target.Column)
            For colno = 1 To lastCol
                If Sheets("TOJ MANGLER").Cells(1, colno) = employee Then
                    colExists = "Y"
                    lastRow = Sheets("TOJ MANGLER").Cells(Rows.Count, colno).End(xlUp).Row
                    Sheets("Material").Range("A" & Target.Row & ":B" & Target.Row).Copy Sheets("TOJ MANGLER").Cells(lastRow + 1, colno)
                    Sheets("TOJ MANGLER").Cells(lastRow + 1, colno + 2) = Target.Value
                End If
            Next
            If colExists = "N" Then
                If Sheets("TOJ MANGLER").Cells(1, 1) = "" Then
                    Sheets("TOJ MANGLER").Cells(1, lastCol) = employee
                    Sheets("Material").Range("A" & Target.Row & ":B" & Target.Row).Copy Sheets("TOJ MANGLER").Cells(2, lastCol)
                    Sheets("TOJ MANGLER").Cells(2, lastCol + 2) = Target.Value
                Else
                    Sheets("TOJ MANGLER").Cells(1, lastCol + 3) = employee
                    Sheets("Material").Range("A" & Target.Row & ":B" & Target.Row).Copy Sheets("TOJ MANGLER").Cells(2, lastCol + 3)
                    Sheets("TOJ MANGLER").Cells(2, lastCol + 5) = Target.Value
                End If

            End If
           
        End If
    End If
End Sub

 

[KlausW]

Hi, check below code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastCol As Integer, colno As Integer, lastRow As Integer
    Dim colExists As String, employee As Integer
    colExists = "N"
    lastCol = Sheets("TOJ MANGLER").Cells(1, Columns.Count).End(xlToLeft).Column
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column > 2 Then
        'MsgBox Target.Column
        If Target.Value <> "" Then
            'MsgBox Target.Value
            employee = Sheets("Material").Cells(1, Target.Column)
            For colno = 1 To lastCol
                If Sheets("TOJ MANGLER").Cells(1, colno) = employee Then
                    colExists = "Y"
                    lastRow = Sheets("TOJ MANGLER").Cells(Rows.Count, colno).End(xlUp).Row
                    Sheets("Material").Range("A" & Target.Row & ":B" & Target.Row).Copy Sheets("TOJ MANGLER").Cells(lastRow + 1, colno)
                    Sheets("TOJ MANGLER").Cells(lastRow + 1, colno + 2) = Target.Value
                End If
            Next
            If colExists = "N" Then
                If Sheets("TOJ MANGLER").Cells(1, 1) = "" Then
                    Sheets("TOJ MANGLER").Cells(1, lastCol) = employee
                    Sheets("Material").Range("A" & Target.Row & ":B" & Target.Row).Copy Sheets("TOJ MANGLER").Cells(2, lastCol)
                    Sheets("TOJ MANGLER").Cells(2, lastCol + 2) = Target.Value
                Else
                    Sheets("TOJ MANGLER").Cells(1, lastCol + 3) = employee
                    Sheets("Material").Range("A" & Target.Row & ":B" & Target.Row).Copy Sheets("TOJ MANGLER").Cells(2, lastCol + 3)
                    Sheets("TOJ MANGLER").Cells(2, lastCol + 5) = Target.Value
                End If

            End If
          
        End If
    End If
End Sub

Good morning Saurabhj, thank you very much, it seems it is just as it should be. Have a nice day, greetings from Denmark, Klaus