Macro - Copy value from the final cell of a column and paste into a cell on another sheet

[canarycat123]

Hi there, I’m hoping that someone can help with this query. I am very new to VBA so apologise in advance if the answer here is obvious. I am trying to achieve the following: Create a macro that can be assigned to a button (in the simple form of a shape) that can perform the following two functions in one swoop:

1. Copy and paste data from a series of cells in the "Data Entry" sheet to the "Master Sheet" (code for this is working).
2. Copy the value of the last populated cell from the end of column "I" in the "Master Sheet", and paste it into cell "H4" of the "Data Entry" sheet (code for this is not working).

The code that I have for section 1 is as follows (this works as required):

Sub CopyData()

Range("C12").Copy
Sheets("Master Sheet").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("C13").Copy
Sheets("Master Sheet").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range("C14").Copy
Sheets("Master Sheet").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

End Sub


The code I have attempted to use for section 2 is as follows:

Sub CopyRef()

Set ws = ActiveWorkbook.Sheets("Master Sheet")
FinalRow = ws.Range("I" & ws.Rows.Count).End(xlUp).Row
ws.Range("I" & FinalRow).Copy
Set ws = ActiveWorkbook.Sheets("Data Entry")
ws.Range("H4").PasteSpecial xlPasteValues

End Sub

The intent is to copy the value of the last populated cell in row “I” of the “Master Sheet”, and paste that into cell “H4” of the “Data Entry” sheet. If that result could then be formatted to specific font, size and colour that would be beneficial.

They are currently both on the same module separated with a line break.

Can anyone tell me where I’m going wrong?

 

[JGordon11]

Try this for section 2:

Sub CopyRef()
    With Sheets("Master Sheet")
        Sheets("Data Entry").Range("H4") = .Range("I" & .Rows.Count).End(xlUp).Value
    End With
End Sub

 

[canarycat123]

Try this for section 2:

Sub CopyRef()
    With Sheets("Master Sheet")
        Sheets("Data Entry").Range("H4") = .Range("I" & .Rows.Count).End(xlUp).Value
    End With
End Sub

Hi there, unfortunately not - this is how I've got it coded currently.

 

[JGordon11]

What happens when you run the Sub CopyRef()?

 

[JGordon11]

For section 1 if the last used row in columns A,B and C in "Master Sheet" are the same you can use:

Sub CopyData()
    Sheets("Master Sheet").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(, 3) = Application.Transpose(Sheets("Data Entry").Range("C12:C14"))
End Sub

 

[canarycat123]

What happens when you run the Sub CopyRef()?

It doesn't appear to do anything. I created a separate module to run on its own, but doesn't appear to function. No errors etc but no result is seen on the sheet.

 

[JGordon11]

What message prints in the immediate window when you run the following code?

Sub Test()
    Dim msg As String, c
    With Sheets("Master Sheet")
        msg = "Master Sheet Last I value: " & .Range("I" & .Rows.Count).End(xlUp).Value & vbCrLf
    End With
    For Each c In Sheets("Data Entry").Range("C12:C14")
        msg = msg & "Data Entry " & c.Address & ":  " & c & vbCrLf
    Next
    Debug.Print msg
End Sub

 

[canarycat123]

What message prints in the immediate window when you run the following code?

Sub Test()
    Dim msg As String, c
    With Sheets("Master Sheet")
        msg = "Master Sheet Last I value: " & .Range("I" & .Rows.Count).End(xlUp).Value & vbCrLf
    End With
    For Each c In Sheets("Data Entry").Range("C12:C14")
        msg = msg & "Data Entry " & c.Address & ":  " & c & vbCrLf
    Next
    Debug.Print msg
End Sub

After running the code there is no message

 

[JGordon11]

The message is printed to the immediate window in the VBA editor (View > Immediate Window). That should make it easy to copy and paste to reply.

 

[canarycat123]

The message is printed to the immediate window in the VBA editor (View > Immediate Window). That should make it easy to copy and paste to reply.

Ah thanks! Didn't know about the immediate window.... that's how new I am to this. The following was produced when the test code was run on its own:

Master Sheet Last I value:
Data Entry $C$12: SS
Data Entry $C$13: CP
Data Entry $C$14: FF