New to VBA and trying to copy and paste

[SirCenturian]

Hi all,

I'm new to the VBA side of Excel and I'm trying to accomplish a statistics page where each day I can run the macro and it will look at a different worksheet and pull various values and paste them in into the relevant cell.

For example, I have Sheet A cell D4 with a value of 120, cell G3 with a value of 32 and cell E29 with a value of 14.

I want those value's from those cells to be pasted into Sheet B cell B3, C3 and D3 respetively.

However on the following day, the Sheet A values have changed and I want the new values to past into row 4 of Sheet B.

I have tried looking around but can't seem to work it out and I though I could ask the hive mind that is the internet.

Many thanks for any help.

 

[MARK858]

Assuming that you have data in Sheet("B") cell B2 when you do the first "paste" then maybe

Sub SirCenturian()
    Dim lr As Long
    lr = Sheets("B").Cells(Rows.Count, "B").End(xlUp).Row + 1

    Sheets("B").Cells(lr, "B").Value = Sheets("A").Cells(4, "D").Value
    Sheets("B").Cells(lr, "C").Value = Sheets("A").Cells(3, "G").Value
    Sheets("B").Cells(lr, "D").Value = Sheets("A").Cells(29, "E").Value

End Sub

 

[JLGWhiz]

And

Sub copyStuff()
Dim rng As Variant
With Sheets(1)
    rng = Array(.Range("D4").Value, .Range("G3").Value, .Range("E29").Value)
End With
        If Sheets(2).Range("B3") = "" Then
            r = 3
        Else
            r = Sheets(2).Cells(Rows.Count, 2).End(xlUp)(2).Row
        End If
        With Sheets(2)
            .Range("B" & r) = rng(0)
            .Range("C" & r) = rng(1)
            .Range("D" & r) = rng(2)
        End With
End Sub

 

[SirCenturian]

Ok i'll give both of those a go and see what I get

also is it easy to add further lines as those were just examples?

 

[MARK858]

also is it easy to add further lines as those were just examples?

Test them based on what you have asked for in post number one then if they work tell us what you actually have as the question is too vague for me to give a definitive answer.

 

[SirCenturian]

Test them based on what you have asked for in post number one then if they work tell us what you actually have as the question is too vague for me to give a definitive answer.

Ok, so I've tested out your code and it works perfectly, now as it's not only me that will be using this but can I add this to a buttom for the other members for ease of use?

 

[MARK858]

Yes if it is a forms button right click it, click assign macro and click the macro name then OK

 

[SirCenturian]

Awesome, thank you so much for your help in this, been wracking my brain with copy/paste values all week haha

 

[MARK858]

Happy we helped and welcome to the forum.