asked on

Simple Move Macro

EE Pros,

I need a macro that will reset values in particular cells (C7:C12) with the values in another set of sells (E7:E12) when the Reset Button is pushed. I would use a formula but the values in the C cells may change until reset is fired.

Also, is there a way to list a set of ranges in one line of code? I usually write numerous lines when I have cells in a group (not using Naming conventions) and it would be helpful to know how to list a string of cell references in one line of code.

Thank you in advance,

B.
Simple-move-with-Macro.xlsm

Rgonzo1971

Hi,

pls try this

    Range(Range("E7"), Range("E35000").End(xlUp)).Copy
    Range("C7").PasteSpecial Paste:=xlPasteValue

Open in new window

for the ranges

you can use if theere is a continuity
Range("C7:E12") or
Range(Range("E7"), Range("C12"))

or you can use unions see ref http://www.vbaexpress.com/kb/getarticle.php?kb_id=354

Regards

Bright01

ASKER

Thaks Rgonzo.

I get a debug error. Can you put the code in the WS and try it? It's "out of range"........

TY,

B.

SOLUTION

Rob Brockett

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Faustulus

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bright01

ASKER

Broro and Faust,

Thank you so much! Broro, I'm now working to learn how to record macros...thank you for the lessons. Very helpful. Faust, I used your code

Sub RefreshCells()
Range("E7:E12").Copy Destination:=Range("C7")
End Sub

To work my problem in my workbook.

Thank you both for your time and patience on this one.

All the best,

B.

Rob Brockett

Thanks for the points B :-)

Faustulus,
Trnasferring (rather than "copying") the values can actually be done in a single line as I showed in my previous post. I also showed a more explicit 3 line version which incorporates a With statement. Here's the one liner:

Range("C7:12").value = Range("E7:E12").value

Open in new window

Rob

Faustulus

Rob,
Yes, of course. I seem to have become stuck in a groove there. :-)