value-paste a range starting from 1st empty row of another sheet

[giorgiotassi]

Hi, I am looking for a VBA solution to a simple problem, for which I have not been able to find an appropriate code.
It is quite a simple task, as illustrated in the enclosed excel file:
- in sheet 1 (= Price Calculation) I have a cost estimate that results in a price calculation.
- in sheet 2 (= offers list) I want to record all prices to keep track of them and possibly filter them if/when needed
I wish to have the essential data of the calculated prices, presented in a range, to be copied and value-pasted from sheet 1 to sheet 2 by pressing a button to this effect.
The complication comes when I want the latest prices range to be value-pasted in the first available = empty row of sheet 2.
I have just started with VBA (enough to be fascinated by its possibilities) and have no experience how to write a code to do this.
Grateful for any help that I will receive.
I'm using Excel 2011 for MAC.
Best regards from Italy.

 

[Luke M]

Welcome to the forum giorgiotassi!

I'm not familiar with VBA for Mac, but hoping this will still work.

Sub TransferData()
Dim lastRow As Long
Dim myRange As Range
Application.ScreenUpdating = False
Set myRange = Worksheets("Price calculation").Range("A8:F11")
With Worksheets("Offers list")
    'Find where last data is
    lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
    myRange.Copy
    .Cells(lastRow + 1, "a").PasteSpecial xlPasteValues
End With
'If you want the previous data cleared, uncomment this next line
'myRange.ClearContents

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[giorgiotassi]

Hi Luke, many thanks for your prompt reply with the code. Fantastic! It works very well. I will surprise my colleagues with this feature in our price calculation sheet and I will give all the credit to you and to Chandoo Forum. Very helpful and most efficient. Again thanks and have a nice weekend.