Bookmark this page Print this page

Spreadsheet, Example Templates, Macros

This help section will show you how to edit macros in your Excel Templates. By following the previous example (see Help section re "Spreadsheet, Example Template") you should have created an Excel macro similar to the one below (though without the help lines written in green)...

Sub CopyData()
'
'Select worksheet called "Data" and cell A2
  Sheets("Data").Select
  Range("A2").Select

'Select range from A2 to the last cell and copy it into memory
  Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
  Selection.Copy

'Select worksheet called "Report" and cell A2, then paste into it
  Sheets("Report").Select
  Range("A2").Select
  ActiveSheet.Paste

'Select range from A2 to the last cell and format width to autofit data
  Range("A1").Select
  Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
  Selection.Columns.AutoFit

End Sub

The above macro is probably too simple for most real life needs, and moreover because the lines ending with ".Select" are equivalent to you clicking on a worksheet or cell, they will slow down the report and have the side effect of causing the screen to flicker while it's running. So here are some tips and examples of how to edit your macro code to make it faster and more flexible (e.g. to copy & paste individual columns rather than the whole worksheet). In all of the following the source data is on the "Data" worksheet starting on row 2 (because row 1 contains the Data Set field names) while the target report is on a worksheet called "Report" starting on row 4 (to allow space for its title)...

'Line to copy and paste the entire of column B of worksheet "Data" starting from row 2 to column D on the worksheet "Report" 

    Sheets("Data").Range("B2:B" & LastDataRow).Copy Sheets("Report").Range("D4:D" & LastRepRow)

'Line to paste "N/A" into columns B and C on the worksheet "Report" 

    Sheets("Report").Range("B4:C" & LastRepRow).Value = "N/A"

The words "LastDataRow" and "LastRepRow" above are not reserved keywords (or commands) in Excel but instead are variables (like "X" or "Y" in algebra) that I have chosen to setup in order to hold the last row numbers of the two worksheets (where the "Report" one is 2 rows deeper because of its extra rows holding the title). To setup such variables you need lines like the following at the start of your code (or anywhere before the variable is required)...

'Setup variables

    Dim LastRepRow, LastDataRow, DataRow, RepRow, RowOffset, ColOffset, LastRowOffset As Integer
    Dim Country, Text, Test, MainType, SubType, LastWord As String
    Dim PaymentDate(10) As Date

And note that you should use the words...

So here are a number of examples of useful lines and routines to show you the correct syntax. For further help you are advised to use Google (e.g. search for "key words in Excel VBA")...

'Find the last row of data in a Column A and add 2 rows for Report's deeper title

    LastDataRow = Sheets("Data").Cells(Sheets("Data").Rows.Count, "A").End(xlUp).Row
    LastRepRow = LastDataRow + 2

'Use of If, then, else (n.b. where "Text" and "RepRow" are variables)

    If Text = ("£") Or Text = ("GBP") Then
        Sheets("Report").Range("M" & RepRow).Value = "£ so N/A"
    Else
        If Text = ("") Then
            Sheets("Report").Range("M" & RepRow).Value = "Default N/A"
        Else
            Sheets("Report").Range("M" & RepRow).Value = "Not £ so Yes"
        End If
    End If

'Copy the value of a cell into a variable called "Country" (with other variables called "RowOffset and ColOffset)
'In the following the country name is actually in a cell at D5, hence RowOffset = 3 and ColOffset = 3, because Col A offset by 3 = D, and Row 2 offset by 3 = 5

    Country = Sheets("Data").Range("A2").Offset(RowOffset, ColOffset).Value

'Paste the value of a variable called "Country" into a cell in column R and on the row number held in the variable "RepRow"

    Sheets("Report").Range("R" & RepRow).Value = Country

'For / Next loop counting down from 20 to 16, copying 5 different column values into variable "Country", testing it's not blank, and if not then pasting its value into a cell, and ending the loop by setting variable "ColOffset" to the loop's end value

    For ColOffset = 20 To 16 Step -1
        Country = Sheets("Data").Range("A2").Offset(RowOffset, ColOffset).Value
        If Country <> ("") Then
            Sheets("Report").Range("T" & RepRow).Value = Country
            ColOffset = 16
        End If
    Next ColOffset

'String manipulation to get the last word of one cell containing a whole address.... 

    '1. Copy the value of the cell into a string variable called "LastWord"...
        LastWord = Sheets("Data").Range("W2").Offset(RowOffset, 0).Value
    '2. Reverse all of the characters in LastWord (because Excel works from left to right, and we want up to the first space between words) e.g. ratlarbiG enaL egelloC 31 sruobraH weN 26 , 001, 01, 81, stinU
        LastWord = StrReverse(LastWord)
    '3. Remove all leading and trailing spaces, just in case
        LastWord = Trim(LastWord)
    '4 Replace all space characters with asterisks (because Excel works more reliably this way) e.g. ratlarbiG*enaL*egelloC*31*sruobraH*weN*26*,*001,*01,*81,*stinU
        LastWord = Replace(LastWord, " ", "*")
    '5 Get the leftmost word up to the first asterisk e.g. 'ratlarbiG*
        LastWord = Left(LastWord, InStr(1, LastWord, "*", vbTextCompare)) 
    '6 Reverse it again to get it the right way around e.g. '*Gibraltar
        LastWord = StrReverse(LastWord) 

'For / Next loop with an array of ten values (where variables inclue "ArrayValue", "PaymentDate(10)", "PaymentMonth", "CurrentMonth", "CurrentSum", "PreviousSum", "RowOffset" and "ColOffset") 

    For ArrayValue = 1 To 10
        PaymentDate(ArrayValue) = Sheets("Data").Range("AU2").Offset(RowOffset, ColOffset).Value
        If PaymentMonth = CurrentMonth Then
            CurrentSum = CurrentSum + Sheets("Data").Range("AV2").Offset(RowOffset, ColOffset).Value
        Else
            PreviousSum = PreviousSum + CurrentSum + Sheets("Data").Range("AV2").Offset(RowOffset, ColOffset).Value
        End If
    Next ArrayValue

'Concatenate 5 cells into one using two For / Next loops (variables include "RowOffset", "ColOffset", "LastRowOffset", "Text", "Test" and "RepRow")

    For RowOffset = 0 To LastRowOffset
        Text = ""
        For ColOffset = 1 To 4
            Test = Sheets("Data").Range("K2").Offset(RowOffset, ColOffset).Value
             If Test <> "" Then
                Text = Text & Test & ", "
            End If
        Next ColOffset
        RepRow = RowOffset + 4
        Sheets("Report").Range("BF" & RepRow).Value = Text
    Next RowOffset

'Format cells for currency where LastRepRow is a variable 

    Sheets("Report").Range("AR4:AW" & LastRepRow).NumberFormat = "$#,##0.00"

'End macro by autofitting the width of all cells in the "Report" worksheet, then selecting the top left cell of it

    Sheets("Report").Cells.Columns.AutoFit
    Sheets("Report").Select
    Range("A2").Select