Macros

This past week I’ve been working on macros to create a flat file since formulas create such a large file and slow it down. Last week I showed in 2 posts the formulas to create a flat file. Today, in a very short post, I will lay out some of the macro functions that I use to create the macros.

First, since I don’t generally write macros and thus am not an expert,  the first thing I do is always use a macro recorder to lay down the foundation of the codes. Once I have that, I look at the VBA code to learn what it does and then start tweaking it to do what I need it to do in a general, as opposed to specific, fashion.

I find that there are a couple of functions or codings that I re-use over and over in the program in order to tweak the recorded macro. Selecting cells are one of the actions that I need to tweak because usually a recorded macro will specify a specific location, such as B1:B9 when I need it to vary according to circumstances.

I find I use the following very frequently:

Next, the things I want the program to do tend to be repeatable over a range of cells, so I generally have a counting code in the program which consist of the following codes:

The counter can be a variable so that the end count will vary according to the circumstances.

Those are the three things I generally use when creating macros. Maybe if I did more macros, I would use more functions. The one thing I don’t do and I probably should is set out the dimensions of the variables. It’s probably a good practice to do so. But I do add in documentation of what I’m trying to do.

[divider]Sample Macro to Create Flat File[/divider]
‘Clear out prior data
Sheets(“Occupancy PY”).Select
Range(“D7:H7”).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
‘Filter your accounts to 5005, 5040, 5055, 5065 to be used for occupancy calc
‘This needs to be done before setting up variables because of counting the number of rows
‘The range noted may be an issue if the filter list needs to grow larger
Sheets(“RDS PY”).Select
ActiveSheet.Range(“$C$9:$C$5615”).AutoFilter Field:=1, Criteria1:=Array( _
“50050000”, “50400000”, “50550000”, “50650000”), Operator:=xlFilterValues
‘ Set up variables
Row = 7 ‘what row you’re on in the flat file
Column = 5 ‘what column (or month) you’re on in the “RDS PY” report tab
Sheets(“RDS PY”).Select
ITEMS = Cells(1, 2) ‘ITEMS variable denotes how many rows of data in the “RDS PY” report tab
FirstRow = 7 ‘the first row in the flat file for copying/pasting month
LastRow = FirstRow + ITEMS ‘the last row in the flat file for copying/pasting month
‘Set up your loops
For Counter = 1 To 12
‘ Copy first three columns – entity, acct no., account name
Sheets(“RDS PY”).Select
Range(“B11:D11”).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
‘ Paste to tab for flat file
Sheets(“Occupancy PY”).Select
Cells(Row, 4).Select ‘If you add columns in “Occupancy PY” tab, you must change the col #
ActiveSheet.Paste
‘ Copy month
Sheets(“RDS PY”).Select
Cells(9, Column).Select
Selection.Copy
‘ Paste month
Sheets(“Occupancy PY”).Select
Range(Cells(FirstRow, 7), Cells(LastRow, 7)).Select ‘If you add columns in “Occupancy PY” tab, you must change the col #
ActiveSheet.Paste
‘ Copy values
Sheets(“RDS PY”).Select
Cells(11, Column).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
‘Paste values
Sheets(“Occupancy PY”).Select
Cells(Row, 8).Select ‘If you add columns in “Occupancy PY” tab, you must change the col #
ActiveSheet.Paste
‘Set your counters for the next loop of copying
Row = Row + ITEMS
Column = Column + 1
FirstRow = FirstRow + ITEMS
LastRow = LastRow + ITEMS
Next Counter
‘After looping ends, next set of instructions finalizes the data
‘Copy the subtotals on “RDS PY” for crosscheck purposes before unfiltering the accounts column in “RDS PY”
Sheets(“RDS PY”).Select
Range(“E1”).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets(“Occupancy PY”).Select
Range(“O1”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
‘Align your formulas with the number of rows of data for pivot table – making sure we capture all rows of data
Sheets(“Occupancy PY”).Select
COUNTFORM = Cells(4, 3) ‘number of rows of formula
COUNTDATA = Cells(4, 4) ‘number of rows of data for pivot table
CopyRow = COUNTFORM + 6 ‘row to copy
StartRow = COUNTFORM + 7 ‘starting position for pasting formulas
EndRow = COUNTDATA + 6 ‘ending position for pasting formulas
Range(Cells(CopyRow, 2), Cells(CopyRow, 3)).Select
Selection.Copy
Range(Cells(StartRow, 2), Cells(EndRow, 3)).Select
ActiveSheet.Paste
‘Extend and refresh your pivot tables
Sheets(“Occupancy PY”).Select
Set PivotRange = Range(Cells(6, 2), Cells(EndRow, 8))
ActiveSheet.PivotTables(“PivotTable1”).ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PivotRange.Address(True, True, xlR1C1, True) _
, Version:=xlPivotTableVersion15)
ActiveSheet.PivotTables(“PivotTable2”).ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PivotRange.Address(True, True, xlR1C1, True) _
, Version:=xlPivotTableVersion15)
‘Unfilter your accounts filter
Sheets(“RDS PY”).Select
ActiveSheet.Range(“$C$9:$C$5615”).AutoFilter Field:=1
End Sub