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

canarycat123

New Member
Joined
Sep 1, 2021
Messages
26
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Active Member
Joined
Jan 18, 2021
Messages
287
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Good, the previous codes should work now that the sheet name problem is resolved.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

canarycat123

New Member
Joined
Sep 1, 2021
Messages
26
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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! (y)
 

canarycat123

New Member
Joined
Sep 1, 2021
Messages
26
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,147,959
Messages
5,744,045
Members
423,843
Latest member
alex2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top