VBA copy/paste from variable workbook to static workbook

[dkovall]

I'm certain this question has been answered numerous times, but I haven't found an answer that fits my particular needs.
I'm trying to copy several cells from 4 different sheets of a variable workbook named by date. It would be saved as "17-Aug-18" today for example.
Then, I'd like to paste the copied data into the next available row of a static worksheet called "Production Master Report".
I haven't done any coding in years, and can't remember the little I did know.
Any help would be GEATLY appreciated!

 

[dkovall]

Mts2018

 

[mumps]

Place this macro in a standard module in each variable workbook and run it from there.

Sub CopyRanges()
    Application.ScreenUpdating = False
    Dim LastRow As Long, x As Long
    Dim desWB As Workbook, srcWB As Workbook, desWS As Worksheet
    Set srcWB = ActiveWorkbook
    Set desWB = Workbooks.Open("X:\CMC\MINE\MINEPLAN\Development Projects\2018 Dev.Projects\Luce Phase E - Stage 2\9.Reports\1. Daily Production Report\Production Report Master.xlsm")
    Set desWS = desWB.Sheets("Sheet1")
    LastRow = desWS.Range("C12:BN154").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    With srcWB.Sheets("Haul")
        desWS.Range("C" & LastRow & ":F" & LastRow).Value = Application.WorksheetFunction.Transpose(.Range("F34:F37").Value)
        desWS.Range("G" & LastRow & ":I" & LastRow).Value = Application.WorksheetFunction.Transpose(.Range("H34:H36").Value)
        desWS.Range("J" & LastRow & ":M" & LastRow).Value = Application.WorksheetFunction.Transpose(.Range("J34:J37").Value)
        desWS.Range("N" & LastRow & ":P" & LastRow).Value = Application.WorksheetFunction.Transpose(.Range("L34:L36").Value)
        desWS.Range("Q" & LastRow).Value = Application.WorksheetFunction.Transpose(.Range("N34").Value)
    End With
    With srcWB.Sheets("Drills")
        desWS.Range("R" & LastRow & ":X" & LastRow).Value = .Range("E30:K30").Value
    End With
    With srcWB.Sheets("Fuel")
        desWS.Range("Y" & LastRow & ":AA" & LastRow).Value = .Range("F10:H10").Value
        desWS.Range("AB" & LastRow & ":AD" & LastRow).Value = .Range("F21:H21").Value
        desWS.Range("AE" & LastRow & ":AG" & LastRow).Value = .Range("F45:H45").Value
        desWS.Range("AH" & LastRow & ":AJ" & LastRow).Value = .Range("F60:H60").Value
        desWS.Range("AK" & LastRow & ":AM" & LastRow).Value = .Range("FF74:H74").Value
    End With
    With srcWB.Sheets("Personnel")
        desWS.Range("AN" & LastRow & ":AV" & LastRow).Value = Application.WorksheetFunction.Transpose(.Range("D10:D18").Value)
        desWS.Range("AW" & LastRow & ":BE" & LastRow).Value = Application.WorksheetFunction.Transpose(.Range("G10:G18").Value)
        desWS.Range("BF" & LastRow & ":BN" & LastRow).Value = Application.WorksheetFunction.Transpose(.Range("J10:J18").Value)
    End With
    Application.ScreenUpdating = True
End Sub

 

[dkovall]

Works great! Thank you, thank you, thank you!!

 

[mumps]

You are very welcome.