Macro for copying values (archive macro)

[chobanne]

Hi all,

I want to make some archive macro in sheet 1, and i dont know how to do that.

In sheet 1 i am calculating something in random cells (example cell B1, B3, and B5). In Sheet 2 I want to make an archive in which i want to store values from sheet 1 in this order - B1 goes to A4, C3 goes to B4, D5 goes to C4. The coping procedure must be only values not formulas. Then If I calculate something else in sheet 1 and start macro again, new values from B1, B3 and B5 must be stored in cells A5, B5, and C5 in sheet 2. Each next click on the macro, values from sheet 1 would be copied in the first next new free row below the last data in sheet 2 in columns A, B and C

Can someone please help me on this

 

[chobanne]

Sorry i saw one mistake in mY post.
The order i wrote is not B1 goes to A4, C3 goes to B4, D5 goes to C4 yet B1 goes to A4, B3 goes to B4, B5 goes to C4

 

[bebo021999]

Option Explicit
Sub transfer()
Dim nextR&
'detect next row in sheet2
nextR = Worksheets("Sheet2").Range("A:C").Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
'paste value
Worksheets("Sheet2").Cells(nextR, 1).Value = Worksheets("Sheet1").Range("B1").Value
Worksheets("Sheet2").Cells(nextR, 2).Value = Worksheets("Sheet1").Range("B3").Value
Worksheets("Sheet2").Cells(nextR, 3).Value = Worksheets("Sheet1").Range("B5").Value
End Sub