Enter Data from one worksheet to another

[N00bExcelUser]

Hello All,

I am still learning a lot of basics with excel, so excuse my ignorance if something similar has already been posted! I'm trying!

I need to take a schedule entered into one sheet (Sheet 1) and have its information entered in another sheet (Sheet 2) in a different format, so that I can enter data accordingly.

Sheet1: "Schedule"


[TABLE="width: 501"]
<tbody>[TR]
[TD]Date[/TD]
[TD]02.04.2018[/TD]
[TD]03.04.2018[/TD]
[TD]04.04.2018[/TD]
[TD]05.04.2018
[/TD]
[/TR]
[TR]
[TD]T1[/TD]
[TD]RH[/TD]
[TD]KW, GB[/TD]
[TD]KK[/TD]
[TD]RH
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T1p[/TD]
[TD]NS[/TD]
[TD]RW[/TD]
[TD]GR[/TD]
[TD]JL[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T2[/TD]
[TD][/TD]
[TD]KW[/TD]
[TD]KW[/TD]
[TD]KK[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T3[/TD]
[TD]JG[/TD]
[TD]SB[/TD]
[TD]GB[/TD]
[TD]PR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T5[/TD]
[TD]JE[/TD]
[TD][/TD]
[TD]RW[/TD]
[TD]JE (NS)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T6[/TD]
[TD][/TD]
[TD]RH[/TD]
[TD][/TD]
[TD]RB[/TD]
[/TR]
</tbody>[/TABLE]


Above is a schedule for employees with their shifts in the left colum, date of the shift in the top row, and the employee who will work that shift initialed in the columns under the dates (if a shift doesn't have an initial then it isn't taking place on that date).

Sheet2: "Sales"

[TABLE="width: 240"]
<tbody>[TR]
[TD="colspan: 3"]Sales List 2018
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Shift
[/TD]
[TD]Employee
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I need is to have this Sheet 2 (Sales) filled in automatically whenever I enter in the schedule (Sheet 1: Schedule). If a shift is to take place then an initial has been entered. In Sheet 2: "Sales" I need to have the date generated in the first column, then the respective shift title (T1, T2, T3, etc.) and the respective employee's initials. Because of the variables here I am just a bit too amateur to figure out a formula at the moment. Anyone have any ideas?

Thanks in advance!

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Schedule" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter your data in each row and exit the cell. The "Sales" sheet will be populated automatically.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Dim lColumn As Long
    lColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Intersect(Target, Range(Cells(2, 2), Cells(LastRow, lColumn))) Is Nothing Then Exit Sub
    Sheets("sheet2").Cells(Sheets("sheet2").Rows.Count, "A").End(xlUp).Offset(1, 0) = Cells(1, Target.Column)
    Sheets("sheet2").Cells(Sheets("sheet2").Rows.Count, "B").End(xlUp).Offset(1, 0) = Cells(Target.Row, 1)
    Sheets("sheet2").Cells(Sheets("sheet2").Rows.Count, "C").End(xlUp).Offset(1, 0) = Target
    Application.ScreenUpdating = True
End Sub