Sub GetFile()
Dim TARFIL, Line, Shift As String
Dim wb As Workbook, wbtemp As Workbook
'wbReport1, wbReport2, wbReport3, wbDateRef As Workbook
Dim ws As Worksheet, wstemp As Worksheet, _
wsReport1 As Worksheet, wsReport2 As Worksheet, _
wsReport3 As Worksheet, wsDateRef As Worksheet
Dim lrow As Long
Set wbtemp = ThisWorkbook 'Raw Data Sheet
Set wsReport1 = wbtemp.Sheets("REPORT1")
Set wsReport2 = wbtemp.Sheets("REPORT2")
Set wsReport3 = wbtemp.Sheets("REPORT3")
Set wsDateRef = wbtemp.Sheets("RYAN")
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
'Setup date as reference per slides
With wsDateRef.Range("R1")
.Value = "Updated as of: " & Format(Now, "mmmm dd, yyyy hh:mm AM/PM")
End With
'Read Data source based on Shift/cut off
If Hour(Now()) >= 6 And Hour(Now()) < 14 Then
Shift = "A-Shift"
Set wstemp = wbtemp.Sheets("A-Shift (raw data)")
TARFIL = "C:\35 - Indust Eng\Inquiry.csv"
'Set wsReport1 = wbReport1.Sheets("REPORT1")
'Set wsReport2 = wbReport2.Sheets("REPORT2")
'Set wsReport3 = wbReport2.Sheets("REPORT3")
ElseIf Hour(Now()) >= 14 And Hour(Now()) < 22 Then
Shift = "B-Shift"
Set wstemp = wbtemp.Sheets("B-Shift (raw data)")
TARFIL = "C:\35 - Indust Eng\Inquiry.csv"
' Set wsReport1 = wbReport1.Sheets("REPORT1")
'Set wsReport2 = wbReport2.Sheets("REPORT2")
' Set wsReport3 = wbReport2.Sheets("REPORT3")
End If
'opening of source data
If Dir(TARFIL) <> "" Then
Workbooks.OpenText Filename:=TARFIL, startRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), _
Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1))
'Activate Data Source
Set wb = ActiveWorkbook
Set ws = wb.Sheets(1)
'Dumping of data to template (Working File)
With ws
lrow = .Range("A" & Rows.Count).End(xlUp).Row
.Columns("F:F").Insert xlToRight
.Range("F2:F" & lrow).Formula = "=A2&E2"
.Range("F2:F" & lrow).Value = .Range("F2:F" & lrow).Value
lrow = .Range("A" & Rows.Count).End(xlUp).Row
.Columns("O:O").Insert xlToRight
.Range("O2:O" & lrow).Formula = "=A2&E2&J2&N2"
.Range("O2:O" & lrow).Value = .Range("O2:O" & lrow).Value
.Range("W2:W" & lrow).Value = Shift
.Range("A4:V" & lrow).Copy wstemp.Range("A1")
End With
'closing of data Source
wb.Close
ProductionSchedule
wbtemp.RefreshAll
End If
End Sub
Private Sub ProductionSchedule()
Dim wbUPH, wbTP As Workbook
Dim wsUPH, wsTP As Worksheet
Dim rng, cel As Range
Dim Uphpath, date_find As String
Uphpath = "C:\Users\NPD\Desktop\Dashboard\Plan.xlsx"
With Application
.DisplayAlerts = False
.AskToUpdateLinks = False
End With
Set wbUPH = Workbooks.Open(Uphpath)
Set wsUPH = wbUPH.Sheets("ProdSched")
Set wbTP = ThisWorkbook
Set wsTP = wbTP.Sheets("UPHReference")
'Set the column as ease
'wsTP.Columns("A:F").Delete xlToLeft
wsTP.Cells.UnMerge
wsTP.Columns("A:F").ClearContents
With wsUPH.Range("AP8:AP39")
.AutoFilter Field:=1, Criteria1:="<>"
.SpecialCells(xlCellTypeVisible).Copy wsTP.Range("A1")
End With
Set rng = wsUPH.Range("AP8:YZ8")
date_find = Format(Now(), "m/d/yyyy")
Set cel = rng.Find(What:=date_find, After:=wsUPH.Range("AP8"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'Debug.Print rng.Address
'Debug.Print date_find
'wsUPH.Cells.MergeCells = False
If Not cel Is Nothing Then
wsUPH.Range(Cells(8, cel.Column), Cells(39, cel.Column + 5)).SpecialCells(xlCellTypeVisible).Copy wsTP.Range("B1")
End If
wsUPH.AutoFilterMode = False
wbUPH.Close
With Application
.DisplayAlerts = True
End With
End Sub
Bookmarks