This article describes how to use an Excel Macro to move all of the footers from a set of exported tables to the tops of the tables. Excel macros use a programming language called Visual Basic to perform repetitive tasks automatically. The macro will move the footer information below the question name. The macro assumes that you have one table per worksheet, and that the table is placed in the default position with the question name in the cell A1.
Method
To use the macro you need to make sure that macros have been enabled and that the Developer tab is shown in the Excel ribbon.
The process for enabling the Developer tab can be found here.
The process for enabling Macros can be found here.
Once macros are enabled and you have exported a batch of tables, add the below macro to your Excel worksheet and run it. To do so:
- Select the Developer tab.
- Click on the Visual Basic button in the top left. This will open a new window called Microsoft Visual Basic for Applications.
- In the window on the left-hand side, you will see a list of folders. Select the folder that contains the name of your workbook.
- Right-click it and select Insert > Module.
- Click on Module 1. This will open a box which will have the name of your workbook and Module 1 at the top.
- Paste the code below into this window.
- Go back to the main Excel window containing your tables.
- Click the Macros button in the top left.
- Click on the macro named FootersToTop.
- Click Run.
Sub FootersToTop()
' Loop over the sheets in the workbook
For Each sht In ActiveWorkbook.Worksheets
' Select the current sheet in the list
sht.Select
'find how long the footer is
'Find the last row
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
'find last row of table
Dim LastRowTab As Long
With ActiveSheet
LastRowTab = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
'calc how many lines the footer is on
Dim footerlines As Long
footerlines = LastRow - LastRowTab
' Insert a blank row(s) at the top
Rows("2:" & (1 + footerlines)).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'Cut and paste footer to top cell
Range("A" & LastRowTab + footerlines + 1 & ":A" & LastRow + footerlines + 1).Select
Selection.Cut
Range("A2").Select
ActiveSheet.Paste
Next sht
End Sub
Next