scott’s spot

So now that we’ve cleaned up some crap recorded code, and we’ve gotten past our first few checkpoints, we want to move the data itself. This is going to involve a couple of steps.

First thing is to clear the target, the place where we’re going to drop the copied data. To do this, we’ll activate the target sheet and place our selection in the very first cell of the range. Then we’ll “end down” the selection to highlight all of it. The last thing we’ll do is ClearContents.

So our code looks like this:

    TB.Activate
    rngTB.Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents

Time for a couple of caveats:

The first is the major one. The “end down” code will select all of the cells in the range PROVIDED THERE ARE NO EMPTY CELLS. If there is one single empty cell in the range, “end down” will stop just above it. So the big danger here is that you don’t select and clear all of the cells in your drop zone.

The second is minor and pertains to how you want to clear the data. If you want to clear the data and leave the formatting (which we have done here) we use .ClearContents. All this does is remove the data. If you want to clear EVERYTHING (formatting included) we’d use .Clear.

Now we’ll move our data. The data we want is spread over a couple of non-contiguous (a fancy way of say “not next to each other”) columns, or we need them in a different order then they appear in the source.

So, in the same way we prepped the target sheet, we activate the source, select the range, “end down” to grab the selection. Then we copy the selection.

Part of the beauty of streamlining this is that we don’t have to activate the target and select the drop zone each time. Here’s where our variable object come in very handy. By simply referencing the worksheet object variable we declared in part 2 of this blog sries, we can paste the data without flipping back and forth between sheets.

    ‘ Let’s activate the styles sheet first.
    Styles.Activate

    ‘ Copy and paste the first range.
    Styles.Range(“E6″).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ThinkBig.Range(“A25″).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False     ‘clear the clipboard
        
    ‘ Copy and paste the second range.
    Styles.Range(“C6″).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ThinkBig.Range(“B25″).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False     ‘clear the clipboard
    
    ‘ Copy and paste the third range.
    Styles.Range(“D6″).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ThinkBig.Range(“D25″).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False     ‘clear the clipboard
    
    ‘ Copy and paste the fourth range.
    Styles.Range(“J6″).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ThinkBig.Range(“E25″).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False     ‘clear the clipboard

You’ll notice that I clear the clipboard after each paste. It’s a easy way to keep the clipboard (and thus the data movement) clean.

So now our code is really coming together. And yet none of this has actually solved the original problem, created when an extra column was added. Sounds like a good topic for tomorrow’s post, part 4.