Copying Cells for one sheet to different ones with conditions

[Cris_93]

Hello Excel masters,

I have a small issue here with populating a file. I have a macro that bring in information to one tab but what I want to develop now is a macro that takes this info from 4 columns and take it to specific tabs and place it there by date. Basically each row is a date.

Can you please explain to me how to do it with one tab and then I will try do do it for all?

So the 4 columns information are "Line", "Prod packs", "Prod Kgs" and "Headcount" this are columns "L", "M", "N" and "O". The date is in cell "A2".

For instance one of the tabs where the info needs to go is tab "Line 1". So on column "L" where the info in "Line 1", the macro needs to take the info regarding the "prod packs", "prod kgs", the"headcount" and the "date" and place it on tab "line 1" on the following columns:
"date"-column C
"Prod packs" - column T
"Prod Kgs" - column S
"Headcount" - column U

All this info needs to be copied to the last available row on each tab because that will be the next day. Also another restriction is that for the same line we can have day and night shift and the information needs to come on different rows.

Can you guys please help me with this? You can see bellow two pictures to show better the files that I'm talking about

Thank you guys,
Cristian

 

[JLGWhiz]

Try this

Sub t()
Dim sh As Worksheet, c As Range
Set sh = Sheet1 'Sheets("Data Import")
    For Each c In sh.Range("L2", sh.Cells(Rows.Count, 12).End(xlUp))
        If c <> 0 And c <> "" Then
            With Sheets("Line " & c.Value)
                .Cells(Rows.Count, 3).End(xlUp)(2) = c.Offset(, -11).Value
                .Cells(Rows.Count, 3).End(xlUp).Offset(, 16).Resize(, 3) = c.Offset(, 1).Resize(, 3).Value
            End With
        End If
    Next
End Sub

 

[JLGWhiz]

use this one

Sub t()
Dim sh As Worksheet, c As Range
Set sh = Sheets("Data Import")
    For Each c In sh.Range("L2", sh.Cells(Rows.Count, 12).End(xlUp))
        If c <> 0 And c <> "" Then
            With Sheets("Line " & c.Value)
                .Cells(Rows.Count, 3).End(xlUp)(2) = c.Offset(, -11).Value
                .Cells(Rows.Count, 3).End(xlUp).Offset(, 16).Resize(, 3) = c.Offset(, 1).Resize(, 3).Value
            End With
        End If
    Next
End Sub

 

[Cris_93]

Hi JLGWhiz,

Thanks for your quick answear but it shows me an error. Please see picture below.

 

[JLGWhiz]

What is the error message? If it is "Subscript out of range" then that means you do not have a worksheet named for that Line. I see in the example worksheet, column L 'Mince2'. If you do not have a worksheet named 'Mince2' then it will give that error. I used column entries to define the worksheets based on your statemnt that

So on column "L" where the info in "Line 1", the macro needs to take the info regarding the "prod packs", "prod kgs", the"headcount" and the "date" and place it on tab "line 1" on the following columns:

I don't know of another way to tie the data to the sheets. I could do it by exception as in the code below, but it might then omit data you want copied over.

:

Sub t2()
Dim sh As Worksheet, c As Range
Set sh = Sheets("Data Import")
    For Each c In sh.Range("L2", sh.Cells(Rows.Count, 12).End(xlUp))
        If c <> 0 And c <> "" Then
            On Error GoTo SKIP:
            With Sheets("Line " & c.Value)
                .Cells(Rows.Count, 3).End(xlUp)(2) = c.Offset(, -11).Value
                .Cells(Rows.Count, 3).End(xlUp).Offset(, 16).Resize(, 3) = c.Offset(, 1).Resize(, 3).Value
            End With
SKIP:
            If Err.Number = 9 Then
                Err.Clear
            ElseIf Err.Number > 0 Then
                MsgBox Err.Number & vbLf & Err.Description, vbOKOnly, "RUN TIME ERROR"
                Exit Sub
            End If
        End If
    Next
End Sub