VBA to Copy to Last Active Cell & Paste Values at Bottom of 'Combined' Sheet

[Small Paul]

Hi

Any help with VBA on this would be appreciated.

I have a workbook, z:\promotional and marketing\product performance\weekly data\cacs.xls which I download each week.
Firstly, I need to Copy cells A2 : last active cell in column R
I then need to Paste Values into z:\promotional and marketing\product performance\cacs history.xls at the bottom of the existing data (presently row 866)

If you could help I would be appreciated.

Thanks
Small Paul.

 

[JackDanIce]

Try:

Sub Macro1()

    Dim w As Workbook
    Dim x As Long
    Dim arr() As Variant

    Const SOURCE As String = "Z:\Promotional and marketing\Product Performance\Monthly Data\Notes June 2018 Paul Testing.xlsx"

    Set w = Workbooks.Open(SOURCE, ReadOnly:=True)
    
    With w
        With .Sheets(1)
            x = .Cells(.Rows.Count, 1).End(xlUp).Row
            arr = .Cells(2, 1).Resize(x - 1, 18).Value
        End With
        .Close Savechanges:=False
    End With
    Set w = Nothing
    
    With ThisWorkbook
        With .Sheets("Perf Report Data")
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
        .SaveAs MyFileName("Z:\Promotional and marketing\Product Performance\Completed Monthly Reports\"), FileFormat:=52
        .Close
    End With
    Erase arr
    
End Sub

Private Function MyFileName(ByRef Folder As String) As String

    Dim str As String
    
    str = "Product Report Paul Testing @1.xlsm"
    MyFileName = Folder & Substitute(str, "@1", Format(DateAdd("m", -1, Now()), "mmm-yyyy"))
    
End Function

 

[Small Paul]

That looks great and reads as though it will work. However:
I am getting 'Compile Error Expected End With' at the 'End Sub'.

 

[JackDanIce]

My error, working on a Mac and not finding it easy after years of Windows.

After .Close add/insert line

End With

 

[Small Paul]

Didn't realise the coding would be different. Shows my knowledge level!!
Anyway, you had that in post #11 :

With ThisWorkbook With .Sheets("Perf Report Data")
.Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
.SaveAs MyFileName("Z:\Promotional and marketing\Product Performance\Completed Monthly Reports"), FileFormat:=52
.Close
End With
Erase arr

End Sub

Private Function MyFileName(ByRef Folder As String) As String

Dim str As String

str = "Product Report Paul Testing @1.xlsm"

This, for me, is the really frustrating bit - I imagine it is something simple and I am sooo close (thanks to you)

 

[JackDanIce]

I only had one End With in that part, but there are two opening With statements; hence the error generated.

Each With should end with an End With

 

[Small Paul]

That works.
Strangely (sorry) it is giving 'Compile Error: Sub or Function Not defined' At "Substitute":

rivate Function MyFileName(ByRef Folder As String) As String

    Dim str As String
    
    str = "Product Report Paul Testing @1.xlsm"
    MyFileName = Folder & Substitute(str, "@1", Format(DateAdd("m", -1, Now()), "mmm-yyyy"))
    
End Function

This seems odd as it looks to be defined in brackets after it. I assume this is requesting to substitute the @1 with the date.

 

[JackDanIce]

Yes you assume correct, however, bad syntax, swap Substitute with Replace

I prefer to use Replace like this than have lots of string concatenations and uses of & and speechmarks everywhere.

 

[Small Paul]

Fantastic

JackDanIce, I thank you very much for your help (and patience) getting this sorted

Cheers
Small Paul.

 

[JackDanIce]

Glad it's resolved