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

Board Regular
Joined
Jan 18, 2021
Messages
246
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

canarycat123

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

Board Regular
Joined
Jan 18, 2021
Messages
246
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

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

Attachments

  • Code1.PNG
    Code1.PNG
    11.3 KB · Views: 3

JGordon11

Board Regular
Joined
Jan 18, 2021
Messages
246
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

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

Board Regular
Joined
Jan 18, 2021
Messages
246
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

And what does this return in the immediate window?

?Sheets("Master Sheet").name
 

JGordon11

Board Regular
Joined
Jan 18, 2021
Messages
246
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

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

Forum statistics

Threads
1,144,700
Messages
5,725,829
Members
422,644
Latest member
BethGS

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