VB Copying values to new row on another sheet

[adrianr]

Hello all,

I am a comlpetely new when it comes to writing code in VB and I would like to explain my problem and ask if anyone has ideas to solve it please help!

Thanks..

Ok my problem is this.
I have a spreadsheet that I would like to setup a Macro.
It has 3 sheets.

Sheet 1 > CommandButton > VB Script
Sheet 2 > Values (changes)
Sheet 3 > Store changelog

Basically I want to be able to press the button on sheet 1 and have it copy the values in sheet 2 to sheet 3 (new row)

I want to copy cells from sheet 2 (A3,A4,A5,A6)
to sheet 3 (A2,B2,C2,D2)

Next time the button is pessed I want it to copy the cells from sheet 2 (A3,A4,A5,A6)
to sheet 3 (A3,B3,C3,D3)

Each time copy the same cells to the next value down.

Sorry if this is not explained well but that is what I would like to do..

The cells A3,A4,A5,A6 are all calculated so I will need to use .value at the end this much I know.

Many thanks,

 

[DauntingGecko]

Option Explicit

Sub CopyValues()

    Dim iValues As Variant
    Dim i As Integer
    
    iValues = [Sheet2].Range("A3:A6").Value
    
    For i = LBound(iValues) To UBound(iValues)
        If Len(iValues(i, 1)) = 0 Then
            MsgBox "Please make sure all values to be copied are included", vbCritical, "Missing Values"
            Exit Sub
        End If
    Next i
    
    [Sheet3].Range("A65536").End(xlUp).Offset(1, 0) = iValues(1, 1)
    [Sheet3].Range("B65536").End(xlUp).Offset(1, 0) = iValues(2, 1)
    [Sheet3].Range("C65536").End(xlUp).Offset(1, 0) = iValues(3, 1)
    [Sheet3].Range("D65536").End(xlUp).Offset(1, 0) = iValues(4, 1)

End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

Possibly this?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Copy_Values()<br>    Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 4).Value _<br>        = Application.Transpose(Sheets("Sheet2").Range("A3:A6").Value)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[adrianr]

Welcome to the MrExcel board!

Possibly this?


Sub Copy_Values()
Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 4).Value _
= Application.Transpose(Sheets("Sheet2").Range("A3:A6").Value)
End Sub

Thank you BOTH so much.. This is great in helping to understand how to write the VB code.

The code here runs much faster..

Now my next challenge is to take this information and produce a graph that updates