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]

That's telling me there is nothing (or what looks like nothing) in the last used row of column I on the Master Sheet. Are the SS, CP, and FF values correct for what you are trying to do?

 

[canarycat123]

Yes - they are correct. The macro copies and pastes that data to the relevant cells, then the adjacent cell in column I contains a Concatenate formula to combine that data into a reference number, and that is what I need to copy and paste back to the "Data Entry" sheet.

 

[JGordon11]

Can you use the xl2bb add-in XL2BB - Excel Range to BBCode to post the relevant parts of the two spreadsheets? Without seeing your sheets and structure it's going to be difficult to determine what's going on.

 

[canarycat123]

Can you use the xl2bb add-in XL2BB - Excel Range to BBCode to post the relevant parts of the two spreadsheets? Without seeing your sheets and structure it's going to be difficult to determine what's going on.

Morning, unfortunately my employer doesn't allow the addition of 3rd party add-ons. I have included a screenshot of the "Master Sheet" - columns A,B & C have been populated with data using the code:

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

I've hidden that data as some is sensitive. Columns E, F & G use a VLOOKUP to return the applicable code, then column I uses the IF&Concatenate formula to generate a reference number. In this example, the second element of the macro would need to copy I2 and paste that to H4 in the "Data Entry" sheet. Then subsequently as more data is added, I3 to H4, I4 to H4 etc.

Does that help?

 

[JGordon11]

What do get if you enter the following in the Immediate window (VBA editor, View > Immediate window)? Paste it in and hit enter - the address returned will be the address of the last used cell in column I.

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

 

[canarycat123]

What do get if you enter the following in the Immediate window (VBA editor, View > Immediate window)? Paste it in and hit enter - the address returned will be the address of the last used cell in column I.

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

That returns an error message saying: "Run-time error '9': Subscript out of range"

 

[JGordon11]

And what does this return in the immediate window?

?Sheets("Master Sheet").name

 

[canarycat123]

And what does this return in the immediate window?

?Sheets("Master Sheet").name

That returns the same error message as before.

 

[JGordon11]

Looks like your worksheet is not named Master Sheet or the workbook is not open. If the workbook is open, then try renaming the Master Sheet it, deleting everything in the name then carefully type "Master Sheet" with no quotes then try again

?Sheets("Master Sheet").name

in the immediate window.

 

[canarycat123]

Looks like your worksheet is not named Master Sheet or the workbook is not open. If the workbook is open, then try renaming the Master Sheet it, deleting everything in the name then carefully type "Master Sheet" with no quotes then try again

?Sheets("Master Sheet").name

in the immediate window.

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