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.

 

[BarryL]

can you try the below? this is to be placed in the cacs history file which will need to change from .xlsx to .xlsm

Sub combineCacs()
Dim wb As Workbook, bk As Workbook, lr As Long
    Set wb = ThisWorkbook
    Set bk = Workbooks.Open("z:\promotional and marketing\product performance\weekly data\cacs.xls")
        Let lr = bk.Sheets(1).Range("R" & bk.Sheets(1).Rows.Count).End(xlUp).Row
            bk.Sheets(1).Range("A2:R" & lr).Copy
            wb.Sheets(1).Range("A" & wb.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row + 1).PasteSpecial
End Sub

 

[Small Paul]

Hi BarryL
Many thanks for the prompt reply.
Unfortunately, being an idiot, I forgot to mention:
a) the worksheet I am pasting into is 'Perf Report Data'

 

[BarryL]

no probs, try the below?

Sub combineCacs()
Dim wb As Workbook, bk As Workbook, lr As Long
    Set wb = ThisWorkbook
    Set bk = Workbooks.Open("z:\promotional and marketing\product performance\weekly data\cacs.xls")
        Let lr = bk.Sheets(1).Range("R" & bk.Sheets(1).Rows.Count).End(xlUp).Row
            bk.Sheets(1).Range("A2:R" & lr).Copy
            wb.Sheets("Perf Report Data").Range("A" & wb.Sheets("Perf Report Data").Range("A" &
Rows.Count).End(xlUp).Row + 1).PasteSpecial
End Sub

 

[Small Paul]

Hi BarryL
Many thanks. It is working great (on a 'test' sheet) until the Paste Values - it is pasting starting at cell A3:

Dim wb As Workbook, bk As Workbook, lr As Long    Set wb = ThisWorkbook
        Set bk = Workbooks.Open("Z:\Promotional and marketing\Product Performance\Monthly Data\Notes June 2018 Paul Testing.xlsx")
        Let lr = bk.Sheets(1).Range("R" & bk.Sheets(1).Rows.Count).End(xlUp).Row
            bk.Sheets(1).Range("A2:R" & lr).Copy
            wb.Sheets("Perf Report Data").Range("A" & wb.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row + 1).PasteSpecial
    ActiveWorkbook.SaveAs Filename:= _
        "Z:\promotional and marketing\product performance\completed monthly reports\ product performance report paul testing " & Format(Now, "mmm-yyyy") & ".xlsm"
End Sub

Small Paul.

 

[Small Paul]

Hi BarryL
I have managed to solve the issue (with your help):

Dim wb As Workbook, bk As Workbook, lr As Long
    Set wb = ThisWorkbook
        Set bk = Workbooks.Open("Z:\Promotional and marketing\Product Performance\Monthly Data\Notes June 2018 Paul Testing.xlsx")
        Let lr = bk.Sheets(1).Range("B" & bk.Sheets(1).Rows.Count).End(xlUp).Row
            bk.Sheets(1).Range("A2:R" & lr).Copy
            wb.Sheets("Perf Report Data").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
    ActiveWorkbook.SaveAs Filename:= _
        "Z:\promotional and marketing\product performance\completed monthly reports\product performance report paul testing " & Format(Now, "mmm-yyyy") & ".xlsm"
End Sub

The only thing it is now failing on is the final Save As

Many thanks
Small Paul.

 

[JackDanIce]

What is the error message that prevents saveas? You've used it earlier in the posted code, was it working before?

 

[Small Paul]

Hi JackDanIce
It is saving as either present month (July 2018) or January 1900.
I have been looking through and have identified (via another Thread) the following format:

Sub Save()Dim FolderPath As String
Dim FileName As String
Dim thisDate As Date
Dim thisMonth As Date
thisDate = Now()
thisMonth = Now()
FileName = "Product Performance Report Paul Testing " & (Format(DateSerial(Month(Date)-1, (Year(Date), Day(Date)), "mm-yyyy"))
FolderPath = "Z:\Promotional and marketing\Product Performance\Completed Monthly Reports\Product Performance Report Paul Testing " & Format(FileName, "mmmm-yyyy") & ".xlsm"
ActiveWorkbook.SaveCopyAs FileName:=sFile
End Sub

However, this is showing 'syntax error' on the 6th row (file name).

Regards
Small Paul.

 

[JackDanIce]

From code in post #6 , steps are:
Open a workbook (Notes June 2018 Paul Testing.xlsx) (1)
Copy data in sheet1 A2:R<last row=""> found in (1)
Paste this data to the workbook containing the code (i.e. ThisWorkbook)

At this point, (1) I believe is the activeworkbook as it's the most recently opened one, so you are saving a .xlsx file as a .xlsm file as per

"Z:\promotional and marketing\product performance\completed monthly reports\product performance report paul testing " & Format(Now, "mmm-yyyy") & ".xlsm"

Is this correct?</last>

 

[Small Paul]

The requirement is much as you say in Post #9 .
a) open workbook - Notes
b) copy data A2:R?
c) paste vales in History (completed) at bottom of existing.
This all works
d) Save History with last month's date
I have tried various ways and can save as this month OR Jan 1900!
I presently have 2 versions, the 2nd where I am simply trying to solve the Save issue:

Dim wb As Workbook, bk As Workbook, lr As Long    Set wb = ThisWorkbook
        Set bk = Workbooks.Open("Z:\Promotional and marketing\Product Performance\Monthly Data\Notes June 2018 Paul Testing.xlsx")
        Let lr = bk.Sheets(1).Range("B" & bk.Sheets(1).Rows.Count).End(xlUp).Row
            bk.Sheets(1).Range("A2:R" & lr).Copy
            wb.Sheets("Perf Report Data").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
    ActiveWorkbook.SaveAs Filename:= _
        "Z:\Promotional and marketing\Product Performance\Completed Monthly Reports\Product Performance Report Paul Testing " & Format(Now() - 1, "mmm-yyyy") & ".xlsm"
End Sub

Dim sFile As StringDim thisDate As Date
Dim thisMonth As Date
thisDate = Now()
thisMonth = Now()
sFile = "Z:\Promotional and marketing\Product Performance\Completed Monthly Reports\Product Performance Report Paul Testing " & Format(thisMonth - 1, "mmm-yyyy") & ".xlsm"
ActiveWorkbook.SaveCopyAs Filename:=sFile
End Sub

From what you say above, the Notes workbook is still the active workbook!