Copy cell content (text) in VBA


How to create Macro that copies the updated content (text) of the same cell each time the Macro is activated (copying the current content).




The code below will do what you want. Paste it into a standard code module. You can hitch it to a button, create a keyboard shortcut to run it, run it from the Macros button on the Developer tab, or run it manually from the VB Editor screen using one of the methods acceptable there. Modify the constants SourceCell and TargetCell to fit your requirements.

Sub CopyCellContent()

    Const SourceCell As String = "A1"
    Const TargetCell As String = "C1"

    Range(TargetCell).Value = Range(SourceCell).Value
End Sub

The code lays bare the deficiencies of your question, however. For example, it copies from and pastes to the active sheet which may or may not be in the workbook that contains the code. There is some probability that you want the SourceCell to be on another sheet and the TargetCell not on the same sheet.

Note that the code expressly only copies SourceCell's value. That makes sense if you intend to format that cell. However, if you wish to copy the cell formats from the SourceCell along with the cell value a different method of copy/pasting should be employed, like,

Range(TargetCell).Copy Destination:=Range(Targetcell)

The code always pastes to the same cell. That isn't very likely your intention. However, you asked how to write such code and here is the answer. Should you face problems adjusting the code please ask your question in a new threat.



I need to run the same macro each time on another workbook which has different value in the same cell that I want to copy into the clipboard and then use the clipboard value as a name of the file for the "save as"  command.
YBB Nov 24, '19 at 12:10 pm
As I said, ask another question. The one you asked was already answered. The one you now describe is, again, insufficiently specific. The Q&A format on this forum doesn't support correspondence. Please ask a precise question and expect an exact answer.
Variatus (rep: 3323) Nov 26, '19 at 9:53 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login