Solved

Simple Move Macro

Posted on 2013-06-22
7
147 Views
Last Modified: 2013-06-28
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
Comment
Question by:Bright01
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39267989
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
 

Author Comment

by:Bright01
ID: 39268242
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
 
LVL 10

Assisted Solution

by:broro183
broro183 earned 250 total points
ID: 39268850
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 14

Accepted Solution

by:
Faustulus earned 250 total points
ID: 39269163
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
 

Author Closing Comment

by:Bright01
ID: 39282549
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
 
LVL 10

Expert Comment

by:broro183
ID: 39285915
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
 
LVL 14

Expert Comment

by:Faustulus
ID: 39286277
Rob,
Yes, of course. I seem to have become stuck in a groove there. :-)
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question