Excel: Efficient Data Management with VBA macros and Formulas

Spreadsheet Examples

• Asia Content.xlsm
• English Content.xlsm

---

Outline

An example of using the FILTER function to auto-fill similar inputs across multiple worksheets, and of reformatting the worksheets and exporting them as PDF files using VBA macros. Created VBA macros are with the help of ChatGPT.

---

Ｍethod

Take spreadsheet Asia Content.xlsm as an example:

• Fill in the data under columns A to M in the "TITLE LIST" sheet. Based on the data filled in, a code will be generated from a formula in column N.
• There is a formula in cell B19 in the sheets titled from "1B. ###" to "13A. ###", which retrieves the codes from column N in the sheet "TITLE LIST" and automatically fills in the data based on different requirements in each sheet.

Formula example:

=IFERROR(FILTER('TITLE LIST'!A:N,('TITLE LIST'!N:N="AENG FMALLN")+('TITLE LIST'!N:N="GMAND FMALLN")+('TITLE LIST'!N:N="OMAND FMALLN")+('TITLE LIST'!N:N="OBM FMALLN")+('TITLE LIST'!N:N="ASOT ONLYALLN")+('TITLE LIST'!N:N="GSOT ONLYALLN")+('TITLE LIST'!N:N="OSOT ONLYALLN")+('TITLE LIST'!N:N="AENG FM05BN")+('TITLE LIST'!N:N="GMAND FM05BN")+('TITLE LIST'!N:N="OMAND FM05BN")+('TITLE LIST'!N:N="OBM FM05BN")+('TITLE LIST'!N:N="ASOT ONLY05BN")+('TITLE LIST'!N:N="GSOT ONLY05BN")+('TITLE LIST'!N:N="OSOT ONLY05BN")+('TITLE LIST'!N:N="GMAND FMALLY")+('TITLE LIST'!N:N="GSOT ONLYALLY")+('TITLE LIST'!N:N="GMAND FM05BY")+('TITLE LIST'!N:N="GSOT ONLY05BY")),"")

• To prevent Excel from lagging while filling in data, I set the Calculation Options to Manual. Hence, users have to run the Calculate Now function every time they finish data entry or make changes. To make this process more convenient for everyone, I created a macro that runs the Calculate Now function and assigned it to a button.

Macro example for function, Calculation Now:

Sub CalculateWorkbook()
  Application.CalculateFull
  MsgBox ("Done.")
End Sub

• Running the macro to tidy up multiple sheets, including hiding and deleting unnecessary columns, rows, and data.

Macro example for reformatting multiple worksheets:

Sub Reformat()
Application.ScreenUpdating = False
For Each sh In Worksheets
If sh.Name <> "INDEX" And sh.Name <> "TITLE LIST" And sh.Name <> "REFORMAT" And sh.Name <> "#" And sh.Name <> "##" Then
  sh.Activate
Dim lRow As Long
Dim iCntr As Long
lRow = 45
For iCntr = lRow To 19 Step -1
  If Trim(Cells(iCntr, 1)) = "" Then
      Rows(iCntr).Delete
  End If
Next
  Range("A19:L45").Select
  Selection.Value = Selection.Value
  Range("K3:L4").Select
  Selection.ClearContents
  Rows("2:2").Select
  Selection.Delete Shift:=xlUp
  Rows("4:16").Select
  Selection.Delete Shift:=xlUp
  x = ActiveSheet.UsedRange.Rows.Count
  Columns("A").Hidden = True
  Columns("G").Hidden = True
End If
Next sh
Application.ScreenUpdating = True
MsgBox ("Done.")
End Sub

• Lastly, running the macro to export multiple sheets as separate PDF files.

Macro example for exporting PDF files:

Sub AllSavePDF()
Dim fileName As String
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
  If ws.Name <> "TITLE LIST" And ws.Name <> "INDEX" And ws.Name <> "##" And ws.Name <> "#" And ws.Name <> "REFORMAT" Then
  
      'Get filename from cell A2
      fileName = ws.Range("A2").Value
      
      'Add date to the filename
      fileName = fileName & "_" & Format(Date, "mm-dd-yy")
      
      'Save as PDF file
      ws.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
          fileName, Quality:=xlQualityStandard, _
          IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End If
Next ws
MsgBox ("Done saving ALL sheets as PDF files!")
End Sub

• In addition, to avoid users forgetting to run Calculation Now after data entry, I created a reminder that will pop out every time they close the workbook.

Macro example for workbook pop out reminder:

  Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Answer As Long
    Dim textBreakLine As String
    Dim textOne As String
    Dim textTwo As String
 
    textBreakLine = "*Producer reminder to run formula*'"
    textOne = "Yes: save & close"
    textTwo = "No: back"
    textThree = "Cancel: close without saving"
    Answer = MsgBox(textBreakLine & vbCrLf & textOne & vbCrLf & textTwo & vbCrLf & textTree, vbQuestion + vbYesNoCancel, "Close Workbook")
    Select Case Answer
        Case vbYes
            ActiveWorkbook.Save
        Case vbNo
            Cancel = True
            ThisWorkbook.Activate
    End Select
End Sub

---

Tutorials

For data entry:

• Desktop User
• Web User

For worksheet reformatting and exporting PDF files:

• Mac User
• Windows User

---