The donut you wish you had not eaten (limbodog) wrote in excel_juice,
The donut you wish you had not eaten

An Easier Way to Move Data

So, being a lazy person, I've been willing to let the record-macro function in Excel do the work for me as often as not. If I wanted to make a macro that moves data from point A to point B, I hit record, copied the cells, and then tweaked the recording as needed.

There are often much better ways that a recorded macro will never show you.

So let's say you want to have a macro that takes data from a cell in one sheet and moves it to a cell in another sheet. Let's say you'll repeat it multiple times for multiple cells.

You could, if you wanted, set up a long series of selecting origin sheet, select cell, copy, select destination sheet, select destination cell, paste... rinse, repeat.


With Sheets("Destination")
.Cells(3, 1).Value = Sheets("Origin").Range("B2").Value
.Cells(4, 1).Value = Sheets("Origin").Range("C2").Value
End With

So very simple and clean! Note: It doesn't seem to work in reverse. The "with" sheet has to be the destination apparently. But you can add 50 lines to that "with:end with" paragraph and it'll chug right through 'em in record time. Also note: you will not see much happening when this goes on. Unlike with the .select VB command, the view doesn't change for simply assigning values.

You can also do a one-shot by simply using
Cells(1, 1).Value = Cells(1, 2).Value
  • Post a new comment


    Anonymous comments are disabled in this journal

    default userpic

    Your IP address will be recorded