[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

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
0
Bright01
Asked:
Bright01
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
Rgonzo1971Commented:
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
0
 
Bright01Author Commented:
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.
0
 
broro183Commented:
hi Bright01,

Have you tried recording a macro?
To do this, open your file, press [alt + t + m + r], perform your desired actions & then look at the generated code. This approach will help you learn & the recorded code will often give you a good starting point.


When I recorded a quick macro the following code was recorded:

Range("E7:E12").Select
    Selection.Copy
    Range("C7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Open in new window


This can be shortened (through experience) to:
Range("E7:E12").Copy
    Range("C7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Open in new window

Or alternatively, my preferred approach (preferred because it doesn't rely on the clipboard) is:
Range("C7:12").value = Range("E7:E12").value

Open in new window

To make this more robust so that it explicitly states which sheet (& in which file) is modified, I would write it as:
    With ThisWorkbook.Worksheets("Assumptions - Benefits & Costs")
        .Range("C7:12").Value = .Range("E7:E12").Value
    End With

Open in new window


Regarding your second question. I used the same approach of recording a macro where I selected multiple ranges using the mouse & then changed the value in all of the selected cells in one go. This is the recorded code:

    Range("E14,F5,C5,C14,B17:C20").Select
    Range("B17").Activate
    Selection.FormulaR1C1 = "23"

Open in new window


With the understanding that it is rarely necessary to "select" or "activate" cells, you could shorten the above code to become:
    Range("E14,F5,C5,C14,B17:C20").FormulaR1C1 = "23"
'or because in this example, the change is just entering a value, it could be written as
    Range("E14,F5,C5,C14,B17:C20").value = "23"

Open in new window


This shows that multi-area ranges can be written in a single line of code within the appostrophes that are inside the pair of brackets (parenthesis?) relating to the range.


Note, that this approach (or using Union) may become slow when there is a very large number of individual ranges involved.

hth
Rob
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
FaustulusCommented:
The single line of code you prefer would be this one:
Sub RefreshCells()
    Range("E7:E12").Copy Destination:=Range("C7")
End Sub

Open in new window

It will copy everything, and if you have any formulas in E7:12 they will be copied, too. Make sure that they are formatted absolute (with $ signs) for columns.

If you would rather copy the values the formulas produce but not the formulas themselves you need more than one line:-
Sub RefreshCells()
    Range("E7:E12").Copy
    Range("C7").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub

Open in new window

I suggest that you use a keyboard shortcut to call either macro.
From the Developer tab select Macros -> Select 'RefreshCells' => Options and enter the shortcut code you like to use. Whether you use this option or not, make sure that the macro is in a standard code module (by default 'Module1').
0
 
Bright01Author Commented:
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.
0
 
broro183Commented:
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
0
 
FaustulusCommented:
Rob,
Yes, of course. I seem to have become stuck in a groove there. :-)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now