File Distribution using VBA

[ollieotis]

Hello,

I use the following code to break up an excel file into multiple files for distribution. In the files being distributed, I would now like to add a sum function at the end of the last row in multiple columns.

Would appreciate any and all help I can get here. I'm a novice at best when it comes to VBA (I received a lot of help in the past from this board on the below code).

Thanks!


Sub DistributeRows()

Dim wbNew As Workbook
Dim wsData As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim rngCrit As Range
Dim LastRow As Long

Set wsData = Worksheets("FileDistribution")
Set wsCrit = Worksheets.Add

LastRow = wsData.Range("AV" & Rows.Count).End(xlUp).Row

wsData.Range("AV1:AV" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True

Set rngCrit = wsCrit.Range("A2")
While rngCrit.Value <> ""
Set wsNew = Worksheets.Add
wsData.Range("A1:BZ" & LastRow).AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=rngCrit.Offset(-1).Resize(2), Unique:=True
wsData.Range("A1:BZ" & LastRow).Copy
wsNew.Range("A1").PasteSpecial xlPasteFormulas
wsNew.Range("A1").PasteSpecial xlPasteFormats
wsNew.Name = rngCrit
Columns("A:BZ").AutoFit

Range("A1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
ActiveSheet.PageSetup.PrintArea = ""
Range("AS:BZ").Delete
wsNew.Copy

Set wbNew = ActiveWorkbook
wbNew.SaveAs ThisWorkbook.Path & "\" & rngCrit, FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
wbNew.Close SaveChanges:=True
Application.DisplayAlerts = False
wsNew.Delete
rngCrit.EntireRow.Delete

Set rngCrit = wsCrit.Range("A2")

Wend

wsData.ShowAllData
wsCrit.Delete
Application.DisplayAlerts = True

End Sub