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?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

JGordon11

Active Member
Joined
Jan 18, 2021
Messages
287
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Try this for section 2:

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

canarycat123

New Member
Joined
Sep 1, 2021
Messages
26
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Try this for section 2:

VBA Code:
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.
 

Attachments

  • Code.PNG
    Code.PNG
    30.2 KB · Views: 9

JGordon11

Active Member
Joined
Jan 18, 2021
Messages
287
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
What happens when you run the Sub CopyRef()?
 

JGordon11

Active Member
Joined
Jan 18, 2021
Messages
287
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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

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

Active Member
Joined
Jan 18, 2021
Messages
287
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

VBA 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

New Member
Joined
Sep 1, 2021
Messages
26
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
What message prints in the immediate window when you run the following code?

VBA 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

Active Member
Joined
Jan 18, 2021
Messages
287
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

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

Forum statistics

Threads
1,147,962
Messages
5,744,057
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