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]

Good, the previous codes should work now that the sheet name problem is resolved.

 

[canarycat123]

Very odd - it now works! Perhaps I'd put an extra space in or something by mistake ?‍

?Sheets("Master Sheet").name returns Master Sheet
?Sheets("Master Sheet").Range("I" & Sheets("Master Sheet").Rows.Count).End(xlUp).Address returns $I$17147

Brilliant! I made a couple more tweaks and it now works, thanks so much for all your help!

 

[canarycat123]

Good, the previous codes should work now that the sheet name problem is resolved.

Morning, I've been playing around with some code for the next element but nothing seems to stick. I'd like to highlight the entire last populated row in the "Master Sheet" in yellow. Do you know the best way of achieving that?