Macro paste into the next blank line

Good morning

I would need some help with a code which will allow me to copy 4 values from one active temporal worksheet (B56:B59) and paste them into another worksheet (A2:D2) named "log". I have managed to do this by using the Transpose option upon pasting (please see code below).

I am struggling with a particular part of the code since I would need theses copied values to be pasted right under the next available blank line.

I would appreciate any help on this.

Thanks in advance

*************************************************************************
Sub save()

ActiveSheet.Shapes("Save_button").Select

Range("B56:B59").Select
Selection.Copy

Sheets("Log").Select

Range("A2").Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True


End Sub
*************************************************************************

Thanks for your reply.

I have add your code but it only highlights the B56:B59 range and does not copy into the log worksheet. Perhaps I am doing something wrong.

The transpose is only used to transform a column data into a row; if it's easier to have a workaround I am happy to modify it, but my main concern is not to overwrite the values in the log worksheet; i.e. paste values in the next blank available row.

Thanks again

The active worksheet is generated temporarily by another macro to do some calculations in the process and it is assigned a name which includes the date; this means the name is dinamically changing so it cannot be defined. Is there any way to define it as active worksheet?

test.xlsPlease have a look at this workbook (Test.xls).

This workbook has two worksheets:

1) "Farmer30-04-12" (temporal worksheet)
2) "Log" (intended repository of results in time)

If you look into the "Farmer30-04-12" worksheet, from top to row A54 there is the calculations (secondary for the macro). In row 54 ("Your Summary of results is") you will find the range of values to be copied to the "log" worksheet. I have implented your code and assigned it to the "Save results to log" button (row 63). If you click on it and turn into the logbook you will see the results.

Values seem to be duplicated several times and if you turn back to "Farmer30-04-12" and click again on "Save results to log" values will be overwritten. Am I missing something?

No worries. Nevertheless, I am getting a run-time error. Please have a look at the images. I'm running an older version of excel (2003); maybe the reason?

run-time error.jpg
debug.jpg

Well done; you're a genious! It is working now brilliantly! Thanks very much for this!

Thumbs up for Canada!