Copy info from sheet1 to an overview sheet with VBA

[nessler]

Hi all,

I was hoping somebody could help me with this one.

I work in a lab, and for one of our analyses we use this excel file.

On sheet 1, "data" we enter our results in the cell range from G10 to G28.
These numbers are then manually copied and pasted into sheet 2, "overview"
in the cell range from C3 to C21, D3 to D21, E3 to E21, etc.

Every time a new analyses is made, we'd like to see the results stored in
the second sheet, and every time in the next availble empty column.

I figured out using the internet that this can be done with a VBA script,
but since I don't know anything from programming I was hoping you folks
could perhaps help me on this one.

Thanks a bunch in advance !

Nick

[davesexcel]

This part is a little confusing,

On sheet 1, "data" we enter our results in the cell range from G10 to G28.
These numbers are then manually copied and pasted into sheet 2, "overview"
in the cell range from C3 to C21, D3 to D21, E3 to E21, etc.

This will copy and paste to the next empty line in column A, change the column letter if you want a different column

Using your macro recorder, you will end up with a code like this

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/14/2008 by Dave Morrison
'

'
    Sheet1.Range("G10:G28").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A65536").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
End Sub

Cleaned up code

Sub CopyToNextSheet()
    Sheet1.Range("G10:G28").Copy Destination:=Sheet2.Range("A65536").End(xlUp).Offset(1, 0)
End Sub

[nessler]

A little more info...

On sheet 1, it looks like this:

......F..............G
10 Moisture....0.29%
11 NH4-N.......7.14%
12 Fe2O3.......2.13%


What we do now, is paste the values in the second overview sheet, so it looks like this, in the F column the previous analysis, G the current ones, and everything from H onwards is currently blank.

........E............F............G...........H
3 Moisture...0.28%......0.29%
4 NH4-N......6.12%......7.14%
5 Fe2O3......2.16%......2.13%

Now what we would like to have, is that when new data is entered in the first sheet and saved,
that the data is automatically stored in the next availble empty column H.
For the next analysis in the next availble empy column I and so on...

Hope this helps