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!

 

[mumps]

The following macro would have to be copied to a regular module in each of the variable workbooks. You would have to create a button named "Export" and assign the macro to it.

Sub CopyRanges()
    Application.ScreenUpdating = False
    Dim lColumn As Long, 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\ProductionReport Master.xlsm")
    Set desWS = desWB.Sheets("Sheet1")
    With srcWB.Sheets("Haul")
        lastRow = desWS.Range("A" & desWS.Rows.Count).End(xlUp).Row
        If lastRow = 1 And Range("A1") = "" Then
            .Range("F34:F37").Copy
            lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column
            desWS.Cells(lastRow, lColumn).PasteSpecial Transpose:=True
            lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column + 1
            .Range("H4:H36").Copy
            desWS.Cells(lastRow, lColumn).PasteSpecial Transpose:=True
            lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column + 1
            .Range("L34:L36").Copy
            desWS.Cells(lastRow, lColumn).PasteSpecial Transpose:=True
            lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column + 1
            .Range("N34").Copy desWS.Cells(lastRow, lColumn)
        Else
            lastRow = desWS.Range("A" & desWS.Rows.Count).End(xlUp).Row + 1
            lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column
            .Range("F34:F37").Copy
            desWS.Cells(lastRow, lColumn).PasteSpecial Transpose:=True
            lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column + 1
            .Range("H4:H36").Copy
            desWS.Cells(lastRow, lColumn).PasteSpecial Transpose:=True
            lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column + 1
            .Range("L34:L36").Copy
            desWS.Cells(lastRow, lColumn).PasteSpecial Transpose:=True
            lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column + 1
            .Range("N34").Copy desWS.Cells(lastRow, lColumn)
        End If
    End With
    With srcWB.Sheets("Drills")
        lastRow = desWS.Range("A" & desWS.Rows.Count).End(xlUp).Row
        lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column + 1
        .Range("E30:K30").Copy desWS.Cells(lastRow, lColumn)
    End With
    With srcWB.Sheets("Fuel")
        lastRow = desWS.Range("A" & desWS.Rows.Count).End(xlUp).Row
        lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column + 1
        .Range("F10:H10").Copy desWS.Cells(lastRow, lColumn)
        lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column + 1
        .Range("F21:H21").Copy desWS.Cells(lastRow, lColumn)
        lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column + 1
        .Range("F45:H45").Copy desWS.Cells(lastRow, lColumn)
        lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column + 1
        .Range("F60:H60").Copy desWS.Cells(lastRow, lColumn)
        lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column + 1
        .Range("F74:H74").Copy desWS.Cells(lastRow, lColumn)
    End With
    With srcWB.Sheets("Personnel")
        lastRow = desWS.Range("A" & desWS.Rows.Count).End(xlUp).Row
        lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column + 1
        For x = 10 To 18
            .Range("C" & x).Resize(, 2).Copy desWS.Cells(lastRow, lColumn)
            lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column + 1
        Next x
        For x = 10 To 18
            .Range("F" & x).Resize(, 2).Copy desWS.Cells(lastRow, lColumn)
            lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column + 1
        Next x
        For x = 10 To 18
            .Range("I" & x).Resize(, 2).Copy desWS.Cells(lastRow, lColumn)
            lColumn = desWS.Cells(lastRow, desWS.Columns.Count).End(xlToLeft).Column + 1
        Next x
    End With
    Application.ScreenUpdating = True
End Sub

 

[dkovall]

Thank you for your time writing that code.
I fear I may have misinformed you a bit. Once I assigned the macro to the export button, it worked, but, it pasted the data in he wrong location.
Is there a way from me to drop both of the excel sheets into this string so you can se exactly what I mean?

 

[mumps]

This Forum doesn't allow you to attach files. Perhaps you could upload a copy of your files to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets.

 

[dkovall]

OK, thank you. I'll have to upload the files from home. Access is blocked from my work PC.

 

[dkovall]

OK, i've uploaded the 2 excel files to dropbox here:
https://www.dropbox.com/s/1v4db1b93tlwv9r/19-Aug-18.xlsx?dl=0
https://www.dropbox.com/s/q0omjp2lcbxifi0/Production Report Master.xlsm?dl=0

The first file, "19-Aug-18" is the variable file I want to capture the data from, cells from 4 sheets as discussed above.
The second file, "Production Report Master" is the static file I want to paste the data into. It has hand entered data through yesterday in it already. The newly copied data would go below existing data in a single row per day.
Is it possible?
Thank you again for all the help!

 

[mumps]

That gives me a better idea of how your data is organized. It is still difficult to tell into which columns of the Production Report Master you want the data pasted. Could you take the current data in the 4 sheets of the variable file you posted and manually paste it into the appropriate locations of Sheet1 of the Production Report Master and highlight that data in yellow and then upload the revised Production Report Master file again. In this way, I can see exactly where you want the data from the 4 sheets to be pasted.

 

[dkovall]

I'll try to explain, because all I'm doing right now is opening up the two sheets side-by-side and manually entering data. All copied cells are in the dynamic workbook named by date, dd-mmm-yy, and are pasted in the static workbook "Production Report Master". We're getting data from a subcontractor daily, and dumping into a "Master" workbook.
I want the data copied from the dynamic workbook to populate the next available row (descending order by date) of he static workbook.

From the "Haul" tab on the variable workbook (19-Aug-18), cell F34 would paste to column C of the static workbook (Production Report Master), next available row, 21 in this case. Then cell F35 to cell D21, F36 to E21, F37 to F21. Then H34 to G21, H35 to H21, and H36 to I21. Then J34 to J21, J35 to K21, J36 to L21, and J37 to M21. Then L34 to N21, L35 to O21, and L36 to P21. Finally, N34 to Q21.
Next, from the "Drills" tab copy cells E30 - K30 and paste into cells R21 - X21 of the Production Report Master.
Next, from the "Fuel" tab copy cells F10 - H10, and paste into cells Y21 - AA21 of the Production Report Master, copy cells F21 - H21 and paste into cells AB21 - AD21, copy cells F45 - H45 and paste into cells AE21 - AG21, copy cells F60 - H60 and paste into cells AH21 - AJ21, and copy cells F74 - H74 and paste into cells AK21 - AM21.
Lastly, from the "Personnel" tab, copy cells D10 - D18, paste into cells AN21 - AV21, copy cells G10 - G18, paste into cells AW21 - BE21, and copy cells J10 - J18 and paste into cells BF21 - BN21.

Hopefully this shows you what I'm trying to do.

Thank you.

 

[dkovall]

I will post the highlighted files to dropbox this evening.

 

[mumps]

OK. Do you still want the macro to open the static workbook or will you open the 2 files manually so that they are both open when the macro is run?

 

[mumps]

In the "Haul" sheet you want to copy J34:J37. Column J is hidden and the sheet is protected. Can you post your password to unprotect it?