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

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.

OR:

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

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your IP address will be recorded 

  • 2 comments

Deleted comment

I actually have been having lots of weird problems with this line. While the example I gave works, in other situations it doesn't.

Doesn't work:
Sheets("Export").Range(Cells(1, 1), Cells(2, 1)).Value = Sheets("Identifiers").Range(Cells(1, 2), Cells(2, 2)).Value

Works:
Sheets("Export").Range(Cells(1, 1), Cells(2, 1)).Value = Sheets("Export").Range(Cells(1, 2), Cells(2, 2)).Value

So while the original example allows you to copy across pages, the one above does not. I have yet to figure out why. Still working on that.
Ah the joys of finally figuring it out.

So anyway, my problem above was that I hadn't qualified the "Cells" arguments.

so Sheets("Export").Range(cells(1, 2), Cells(2, 2)).Value

should have been:

Sheets("Export").Range(Sheets("Export").cells(1, 2), Sheets("Export").Cells(2, 2)).Value

Otherwise it was assuming that the cells were in the active sheet "Identifiers" which was a conflict.