scott’s spot

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!