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.

Now that we’ve eliminated the most obviously unneeded lines of VBA from the routine, we can start shaping this into something a little smoother.

The first thing I did was to add comments. I find, whether I’m coding for myself or a business partner, or whether I’m running through somebody else’s code and trying to understand its flow and function, by adding comments in plain old english, I get where I need to go just that much faster.

To that end, I added a couple of lines about what exactly we were doing with this routine right after I declared it.

Submco_Populate()
‘   This routine moves data back and forth between two sheets
‘   in the same workbook, after first clearing out the old data,
‘   and ensuring that an autofilter is present.
‘
‘   Author: Scott Lyerly, scott[dot]c[dot]lyerly[at]hotmail[dot]com.
‘   Date: 5/7/2009.

I like to “sign and date” my code so that people know who did what and when (and how to get hold of me in case something goes wrong and needs to be fixed–not that anything ever goes wring in my code, of course…).

Once done, it was time to start making this thing over. I began by declaring some variables and then assigning values and objects to them:

    ‘ Variable declarations.
    DimThisBook As Workbook
    Dim Styles As Worksheet, TB As Worksheet
    DimrngTB As Range

    ‘ Set the variables
    Set ThisBook = ThisWorkbook
    Set Styles = ThisBook.Sheets(“Styles”)
    Set TB = ThisBook.Sheets(“TB”)
    Set rngTB = ThinkBig.Range(“A25:F26″)

First I declare the variables, then I assign them. Now, whenever I want to reference one of them, I don’t need to write something like:

ThisWorkbook.Sheets(“TB”).Range (“A25:F26″)

I can now just activate the sheet and reference the range variable:

   TB.Activate
   rngTB.Select

This makes it worlds easier to maneuver through the workbook objects (books, sheets, ranges, cells, etc.). And if I need to change one (say the range for example) I change it in one spot, the place where I assigned it, rather than in each instance where the macro recorded (and hard-coded) the range reference.

The next thing I wanted to do was to ensure an autofilter was applied to the range from which I’ll copy. Since part of this process hinges on a certain field being filtered for “Y”, I felt it was important to double-check and make sure the filter was actually present.

To do this, I decided to declare a constant first:

    ‘ Constant delcarations.
    Const FilterCrit As String = “Y”

This was one of those thing I probably didn’t need to do, since we only apply the filter once, and it’s always “Y”, but I like things neat and tidy and having this “Y” as a constant gives me complete control over it. Now, instead of using “Y” in the filter criteria in the code, I can use the constant. And if I need to change the constant for some reason, I change it in its declaration, and lo! it’s good throughout the whole of the code anywhere it shows itself.

Then I needed to check to make sure a filter was applied:

    ‘ Check to make sure an autofilter is actually present.
    If NotStyles.AutoFilterMode Then
        MsgBox “There’s no autofilter here. Please apply an autofilter before continuing.”, vbOKOnly + vbExclamation
    Exit Sub
    End If

    ‘ If the our sub-routine is still running at this point, it’s safe to
    ‘ assume an autofilter exists in the sheet in question. Now we simply
    ‘ apply our constant, the criteria, to the filter.
    Selection.AutoFilter Field:=1, Criteria1:=FilterCrit.

And at the end of this snippet, I assign the constant to the appropriate autofilter field, which causes the sheet to become filtered.

So…we’ve streamlined out workbook object references, and we’ve made sure an autofilter exists and have applied it.

Our code clean-up to date:

Submco_Populate()
‘   This routine moves data back and forth between two sheets
‘   in the same workbook, after first clearing out the old data,
‘   and ensuring that an autofilter is present.
‘
‘   Author: Scott Lyerly, scott[dot]c[dot]lyerly[at]hotmail[dot]com.
‘   Date: 5/7/2009.

 
    ‘ Constant delcarations.
    Const FilterCrit As String = “Y”

 
    ‘ Variable declarations.
    DimThisBook As Workbook
    Dim Styles As Worksheet, TB As Worksheet
    DimrngTB As Range
    ‘ Set the variables
    Set ThisBook = ThisWorkbook
    Set Styles = ThisBook.Sheets(“Styles”)
    Set TB = ThisBook.Sheets(“TB”)
    Set rngTB = ThinkBig.Range(“A25:F26″)

 
    ‘ Activate the sheet that will be the source of our data.
     Styles.Activate

 
    ‘ Check to make sure an autofilter is actually present.
    If NotStyles.AutoFilterMode Then
        MsgBox “There’s no autofilter here. Please apply an autofilter before continuing.”, vbOKOnly + vbExclamation
    Exit Sub
    End If

    ‘ If the our sub-routine is still running at this point, it’s safe to
    ‘ assume an autofilter exists in the sheet in question. Now we simply
    ‘ apply our constant, the criteria, to the filter.
    Selection.AutoFilter Field:=1, Criteria1:=FilterCrit

On to the movement of data…our topic for tomorrow.

A firend of mine dropped me a line a few weeks ago regarding a macro he was having trouble with. This friend is a good guy, and he knows a bit of VBA, but not enough to really rework something. It’s a kind of “just enough to be dangerous” scenario.

Anyway, he had recorded a macro that applied a criteria to one of the filters in an autofilter on a specific sheet, and then copy/pasted data from various columns and rows to a different sheet. Problem is, at some point he needed to add a column to the source sheet. Suddenly everything stopped working correctly.

Sounds easy enough, right? Straight-forward to correct? Sure.

Except it’s not.

Because, when you use Excel’s macro recorder, you get what can only be described as a whole bunch of crap. The recorder adds a ton of stuff that you do that you don’t really need. Scrolling is a good example of this. Does anyone actually need to automate scrolling? I never have. But hey, maybe I’m missing something.

In any event, the code snippet my friend sent to me is below (names changed to protect the innocent!). What I thought I’d do is, over the next few posts, show what I did to clean the code up.

First his code. And right off the bat, I eliminated the ActiveWindow.ScrollRow, which automatically made his code smaller:

Sub mco_Populate()
‘
‘ mco_Populate Macro
‘ Macro recorded 4/2/2009 by
‘ Keyboard Shortcut: Ctrl+k
‘
Sheets(“Styles”).Select
Selection.AutoFilter Field:=1, Criteria1:=”Y”
Sheets(“TB”).Select
Range(“A25:F26″).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets(“Styles”).Select
Range(“E6″).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets(“TB”).Select
Range(“A25″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(“Styles”).Select
ActiveWindow.ScrollRow = 854
ActiveWindow.ScrollRow = 852
ActiveWindow.ScrollRow = 847
ActiveWindow.ScrollRow = 836
ActiveWindow.ScrollRow = 826
ActiveWindow.ScrollRow = 803
ActiveWindow.ScrollRow = 747
ActiveWindow.ScrollRow = 660
ActiveWindow.ScrollRow = 563
ActiveWindow.ScrollRow = 491
ActiveWindow.ScrollRow = 422
ActiveWindow.ScrollRow = 382
ActiveWindow.ScrollRow = 325
ActiveWindow.ScrollRow = 264
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 6
Range(“C6″).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“TB”).Select
Range(“B25″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(“Styles”).Select
ActiveWindow.ScrollRow = 854
ActiveWindow.ScrollRow = 852
ActiveWindow.ScrollRow = 844
ActiveWindow.ScrollRow = 839
ActiveWindow.ScrollRow = 824
ActiveWindow.ScrollRow = 806
ActiveWindow.ScrollRow = 780
ActiveWindow.ScrollRow = 719
ActiveWindow.ScrollRow = 604
ActiveWindow.ScrollRow = 514
ActiveWindow.ScrollRow = 417
ActiveWindow.ScrollRow = 341
ActiveWindow.ScrollRow = 272
ActiveWindow.ScrollRow = 251
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 233
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 187
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 6
Range(“D6″).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“TB”).Select
Range(“D25″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(“Styles”).Select
ActiveWindow.ScrollRow = 854
ActiveWindow.ScrollRow = 852
ActiveWindow.ScrollRow = 844
ActiveWindow.ScrollRow = 836
ActiveWindow.ScrollRow = 816
ActiveWindow.ScrollRow = 788
ActiveWindow.ScrollRow = 729
ActiveWindow.ScrollRow = 619
ActiveWindow.ScrollRow = 509
ActiveWindow.ScrollRow = 420
ActiveWindow.ScrollRow = 333
ActiveWindow.ScrollRow = 259
ActiveWindow.ScrollRow = 216
ActiveWindow.ScrollRow = 167
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 6
Range(“J6″).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“TB”).Select
Range(“E25″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(“A1″).Select
End Sub

became

Sub mco_Populate()
‘
‘ mco_Populate Macro
‘ Macro recorded 4/2/2009 by
‘ Keyboard Shortcut: Ctrl+k
‘
Sheets(“Styles”).Select
Selection.AutoFilter Field:=1, Criteria1:=”Y”
Sheets(“TB”).Select
Range(“A25:F26″).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets(“Styles”).Select
Range(“E6″).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets(“TB”).Select
Range(“A25″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(“Styles”).Select
Range(“C6″).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“TB”).Select
Range(“B25″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(“Styles”).Select
Range(“D6″).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“TB”).Select
Range(“D25″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(“Styles”).Select
Range(“J6″).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“TB”).Select
Range(“E25″).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(“A1″).Select
End Sub

Very quickly this seems a lot more manageable. Tomorrow, we’ll streamline his references and make sure the sheet is ready for some copy/paste action!