CODEWITHSUNDEEP
excelvbavlookup
user6691335
user6691335

Reputation:

Excel: VBA to copy content from cell in active row

I need help to assign a function to a button in excel. What i want to do is have a button get the value of the first column in the active row and copy this to another cell, or better assign this as a "variable" to be used in a lookup formula of a cell.

I have tried some variants, for example this:

Range("A" & (ActiveCell.Row)).Copy ("H2")

This gives

Copy method of Range class failed

I'm a beginner in VBA scripting so I don't really know how to move forward with this.

Upvotes: 0

Views: 5943

Answers (3)

Ralph
Ralph

Reputation: 9444

I'd assume that you are looking for something like this:

Range("A" & (ActiveCell.Row)).Copy Destination:=Range("H2")

Yet, you should insert a procedure for that purpose and not a function. So, the complete code (including a more explicit coding style) could be something like this:

Option Explicit

Public Sub tmpSO()

Dim ws As Worksheet

Set ws = ThisWorkbook.ActiveSheet
ws.Range("A" & (ActiveCell.Row)).Copy Destination:=ws.Range("H2")

End Sub

Upvotes: 1

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

Try with any of the below.

Range("A" & (ActiveCell.Row)).Copy [H2]

Range("A" & (ActiveCell.Row)).Copy Range("H2")

Upvotes: 0

user3598756
user3598756

Reputation: 29421

since you wrote you're interested in cells value: 

Range("H2").value = Cells(ActiveCell.row, 1).value

Upvotes: 1

Related Questions