Create Macro to change a specific ( variable ) cell value to a new value found in another cell.

[ricky bobby]

I'm pretty new at excel macro's but this is what I'm trying to do. Any help is appreciated.

I need to create a command button macro to take a value from a fixed cell address and enter the data at another variable cell address that is entered at a fixed cell address.

ie.<o class="" style="box-sizing: border-box;"></o>
Cell K6 has the value that i need to post. The value in K6 will change upon user input at another cell. <o class="" style="box-sizing: border-box;"></o>
Cell K7 has the address of the cell where I need to post it. The address in K7 will change upon user input at another cell. An example of an address in K7 looks like this... $J$12, I could probably get it to show without the symbols if needed. (J12)<o class="" style="box-sizing: border-box;"></o>
Any thoughts?

 

[V_Malkoti]

Range(Range("K7").Value).Value = Range("K6")

 

[ricky bobby]

Range(Range("K7").Value).Value = Range("K6")

Thank you Malkoti, this code works perfectly when I insert the command button on sheet2 which is where the K7 and K6 cells are. When I insert the command button on Sheet1, I get an error. I'm wondering if the "Private Sub" is the issue in this command?

Private Sub CommandButton1_Click()
Range(Range(Sheet2!"K7").Value).Value = Range(Sheet2!"K6")
End Sub

Any thoughts on this?

 

[V_Malkoti]

What is the error that you get?

 

[ricky bobby]

When written as above it says expected separator or ). If I write is as..... Range(Range("Sheet2!K7").Value).Value = Range("Sheet2!K6"), the error code is "invalid range of method

 

[V_Malkoti]

Use sheets object to specify sheet name:

Sheets("SheetNameHere").Range(Sheets("Sheet2").Range("K7").Value).Value = Sheets("Sheet2").Range("K6")

 

[ricky bobby]

Yep, that's awesome!! It worked perfectly. Thank you so much for your help!!

 

[V_Malkoti]

You are welcome!