Scribd
Upload
31 views6 pages

Excel VBA Clean Up Report Automation

This VBA macro defines variables to store the last row and column of multiple worksheets. It then sets the worksheets being used as the source and output sheets. Formulas are added to the output sheet to summarize data from the source sheets, including cleaning up DC names, calculating the number of months in a data set, populating the sector, and summing values by month for a range of cells on the output sheet.

Uploaded by

api-621467446
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
31 views6 pages

Excel VBA Clean Up Report Automation

This VBA macro defines variables to store the last row and column of multiple worksheets. It then sets the worksheets being used as the source and output sheets. Formulas are added to the output sheet to summarize data from the source sheets, including cleaning up DC names, calculating the number of months in a data set, populating the sector, and summing values by month for a range of cells on the output sheet.

Uploaded by

api-621467446
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Module1 - 1

Sub Clean_Up_Report()

Dim SourceLastRow As Long


Dim SourceLastRow1 As Long
Dim SourceLastRow2 As Long
Dim SourceLastRow3 As Long
Dim SourceLastRow4 As Long

Dim OutputLastRow As Long

Dim SourceLastColumn As Long


Dim SourceLastColumn1 As Long
Dim SourceLastColumn2 As Long
Dim SourceLastColumn3 As Long
Dim SourceLastColumn4 As Long

Dim sourceSheet As Worksheet


Dim sourceSheet1 As Worksheet
Dim sourceSheet2 As Worksheet
Dim sourceSheet3 As Worksheet
Dim sourceSheet4 As Worksheet

Dim outputSheet As Worksheet

Set sourceSheet = Worksheets("iTrade Report")


Set sourceSheet1 = Worksheets("MSR")
Set sourceSheet2 = Worksheets("FromTo")
Set sourceSheet3 = Worksheets("Sysco OG")
Set sourceSheet4 = Worksheets("Sysco Stock Matrix")

Set outputSheet = Worksheets("Clean Up")

With sourceSheet
SourceLastRow = .Cells(.[Link], "K").End(xlUp).Row
End With

With sourceSheet
SourceLastColumn = .Cells(.[Link], "K").End(xlUp).Column
End With

With sourceSheet1
SourceLastRow1 = .Cells(.[Link], "J").End(xlUp).Row
End With

With sourceSheet1
SourceLastColumn1 = .Cells(.[Link], "J").End(xlUp).Column
End With

With sourceSheet2
SourceLastRow2 = .Cells(.[Link], "A").End(xlUp).Row
End With

With sourceSheet2
SourceLastColumn2 = .Cells(.[Link], "A").End(xlUp).Column
End With

With sourceSheet3
SourceLastRow3 = .Cells(.[Link], "A").End(xlUp).Row
End With

With sourceSheet3
SourceLastColumn3 = .Cells(.[Link], "A").End(xlUp).Column
End With

With sourceSheet4
SourceLastRow4 = .Cells(.[Link], "A").End(xlUp).Row
End With

With sourceSheet4
SourceLastColumn4 = .Cells(.[Link], "A").End(xlUp).Column
End With
Module1 - 2

'Copy Product Number, Product Name, Pack Size, and Description


LastRow = [Link]([Link], 3).End(xlUp).Row
[Link]("E13:H13", "E" & LastRow).Copy
[Link]("F12").PasteSpecial xlPasteValues
[Link]

'Copies DC Number, DC Name, and Decentralized National ID Number


LastRow = [Link]([Link], 3).End(xlUp).Row
[Link]("A13:C13", "A" & LastRow).Copy
[Link]("A12:C12").PasteSpecial xlPasteValues
[Link]

'DC Name on iTrade Report


With sourceSheet
SourceLastRow = .Cells(.[Link], "M").End(xlUp).Row
.Range("O2:O" & SourceLastRow).Formula = _
"=SUBSTITUTE($M2,""SYSCO "","""")"
End With

'Define Number of months on MSR


With sourceSheet1
SourceLastRow1 = .Cells(.[Link], "A").End(xlUp).Row
.Range("K2").Formula = _
"=SUMPRODUCT((D12:D100000 <> """")/COUNTIF(D12:D100000,D12:D100000 & """"))"
.Range("K2").[Link] = vbWhite

End With

'Populate Sector on CleanUp Sheet


'Part I
With outputSheet
OutputLastRow = .Cells([Link], "A").End(xlUp).Row
.Range("AY12:AY" & OutputLastRow).Formula = _
"=IFERROR(INDEX($AZ$12:$AZ$17,AGGREGATE(15,6,MATCH(""*""&$AZ$12:$AZ$17&""*"",$C12,0)*(ROW($AZ$
12:$AZ$17)-ROW(AZ$12)+1),1)),"""")"
End With

'Part II
With outputSheet
OutputLastRow = .Cells([Link], "A").End(xlUp).Row
.Range("D12:D" & OutputLastRow).Formula = _
"=IF($AY12=$AZ$12,$BA$12,IF($AY12=$AZ$13,$BA$13,IF($AY12=$AZ$14,$BA$14,IF($AY12=$AZ$15,$BA$15,
IF($AY12=$AZ$16,$BA$16,IF($AY12=$AZ$17,$BA$17,""""))))))"
End With

'Fill in Order Guide Type


With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("E12:E" & OutputLastRow).Formula = _
"=IF($D12=$BA$12,$BB$12,IF($D12=$BA$13,$BB$13,IF($D12=$BA$14,$BB$14,IF($D12=$BA$15,$BB$15,IF($
D12=$BA$16,"""",IF($D12=$BA$17,$BB$17,""""))))))"
End With

'MONTHLY SUMMARIES

'Sum for Month of January


With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("J12:J" & OutputLastRow).Formula = _
"=SUMPRODUCT(('" & [Link] & "'!$I$13:$I$" & SourceLastRow1 & ")*('" & sourceShe
[Link] & "'!D$13:D$" & SourceLastRow1 & "=$J$10)*('" & [Link] & "'!A$13:A$" & SourceLastR
ow1 & "=$A12)*('" & [Link] & "'!C$13:C$" & SourceLastRow1 & "=$C12)*('" & [Link]
& "'!E$13:E$" & SourceLastRow1 & "=$F12))"
End With
'Sum for Month of February
With outputSheet
Module1 - 3

OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row


.Range("K12:K" & OutputLastRow).Formula = _
"=SUMPRODUCT(('" & [Link] & "'!$I$13:$I$" & SourceLastRow1 & ")*('" & sourceShe
[Link] & "'!D$13:D$" & SourceLastRow1 & "=$K$10)*('" & [Link] & "'!A$13:A$" & SourceLastR
ow1 & "=$A12)*('" & [Link] & "'!C$13:C$" & SourceLastRow1 & "=$C12)*('" & [Link]
& "'!E$13:E$" & SourceLastRow1 & "=$F12))"
End With
'Sum for Month of March
With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("L12:L" & OutputLastRow).Formula = _
"=SUMPRODUCT(('" & [Link] & "'!$I$13:$I$" & SourceLastRow1 & ")*('" & sourceShe
[Link] & "'!D$13:D$" & SourceLastRow1 & "=$L$10)*('" & [Link] & "'!A$13:A$" & SourceLastR
ow1 & "=$A12)*('" & [Link] & "'!C$13:C$" & SourceLastRow1 & "=$C12)*('" & [Link]
& "'!E$13:E$" & SourceLastRow1 & "=$F12))"
End With
'Sum for Month of April
With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("M12:M" & OutputLastRow).Formula = _
"=SUMPRODUCT(('" & [Link] & "'!$I$13:$I$" & SourceLastRow1 & ")*('" & sourceShe
[Link] & "'!D$13:D$" & SourceLastRow1 & "=$M$10)*('" & [Link] & "'!A$13:A$" & SourceLastR
ow1 & "=$A12)*('" & [Link] & "'!C$13:C$" & SourceLastRow1 & "=$C12)*('" & [Link]
& "'!E$13:E$" & SourceLastRow1 & "=$F12))"
End With
'Sum for Month of May
With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("N12:N" & OutputLastRow).Formula = _
"=SUMPRODUCT(('" & [Link] & "'!$I$13:$I$" & SourceLastRow1 & ")*('" & sourceShe
[Link] & "'!D$13:D$" & SourceLastRow1 & "=$N$10)*('" & [Link] & "'!A$13:A$" & SourceLastR
ow1 & "=$A12)*('" & [Link] & "'!C$13:C$" & SourceLastRow1 & "=$C12)*('" & [Link]
& "'!E$13:E$" & SourceLastRow1 & "=$F12))"
End With
'Sum for Month of June
With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("O12:O" & OutputLastRow).Formula = _
"=SUMPRODUCT(('" & [Link] & "'!$I$13:$I$" & SourceLastRow1 & ")*('" & sourceShe
[Link] & "'!D$13:D$" & SourceLastRow1 & "=$O$10)*('" & [Link] & "'!A$13:A$" & SourceLastR
ow1 & "=$A12)*('" & [Link] & "'!C$13:C$" & SourceLastRow1 & "=$C12)*('" & [Link]
& "'!E$13:E$" & SourceLastRow1 & "=$F12))"
End With
'Sum for Month of July
With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("P12:P" & OutputLastRow).Formula = _
"=SUMPRODUCT(('" & [Link] & "'!$I$13:$I$" & SourceLastRow1 & ")*('" & sourceShe
[Link] & "'!D$13:D$" & SourceLastRow1 & "=$P$10)*('" & [Link] & "'!A$13:A$" & SourceLastR
ow1 & "=$A12)*('" & [Link] & "'!C$13:C$" & SourceLastRow1 & "=$C12)*('" & [Link]
& "'!E$13:E$" & SourceLastRow1 & "=$F12))"
End With
'Sum for Month of August
With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("Q12:Q" & OutputLastRow).Formula = _
"=SUMPRODUCT(('" & [Link] & "'!$I$13:$I$" & SourceLastRow1 & ")*('" & sourceShe
[Link] & "'!D$13:D$" & SourceLastRow1 & "=$Q$10)*('" & [Link] & "'!A$13:A$" & SourceLastR
ow1 & "=$A12)*('" & [Link] & "'!C$13:C$" & SourceLastRow1 & "=$C12)*('" & [Link]
& "'!E$13:E$" & SourceLastRow1 & "=$F12))"
End With
'Sum for Month of September
With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("R12:R" & OutputLastRow).Formula = _
"=SUMPRODUCT(('" & [Link] & "'!$I$13:$I$" & SourceLastRow1 & ")*('" & sourceShe
[Link] & "'!D$13:D$" & SourceLastRow1 & "=$R$10)*('" & [Link] & "'!A$13:A$" & SourceLastR
ow1 & "=$A12)*('" & [Link] & "'!C$13:C$" & SourceLastRow1 & "=$C12)*('" & [Link]
& "'!E$13:E$" & SourceLastRow1 & "=$F12))"
End With
'Sum for Month of October
Module1 - 4

With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("S12:S" & OutputLastRow).Formula = _
"=SUMPRODUCT(('" & [Link] & "'!$I$13:$I$" & SourceLastRow1 & ")*('" & sourceShe
[Link] & "'!D$13:D$" & SourceLastRow1 & "=$S$10)*('" & [Link] & "'!A$13:A$" & SourceLastR
ow1 & "=$A12)*('" & [Link] & "'!C$13:C$" & SourceLastRow1 & "=$C12)*('" & [Link]
& "'!E$13:E$" & SourceLastRow1 & "=$F12))"
End With
'Sum for Month of November
With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("T12:T" & OutputLastRow).Formula = _
"=SUMPRODUCT(('" & [Link] & "'!$I$13:$I$" & SourceLastRow1 & ")*('" & sourceShe
[Link] & "'!D$13:D$" & SourceLastRow1 & "=$T$10)*('" & [Link] & "'!A$13:A$" & SourceLastR
ow1 & "=$A12)*('" & [Link] & "'!C$13:C$" & SourceLastRow1 & "=$C12)*('" & [Link]
& "'!E$13:E$" & SourceLastRow1 & "=$F12))"
End With
'Sum for Month of December
With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("U12:U" & OutputLastRow).Formula = _
"=SUMPRODUCT(('" & [Link] & "'!$I$13:$I$" & SourceLastRow1 & ")*('" & sourceShe
[Link] & "'!D$13:D$" & SourceLastRow1 & "=$U$10)*('" & [Link] & "'!A$13:A$" & SourceLastR
ow1 & "=$A12)*('" & [Link] & "'!C$13:C$" & SourceLastRow1 & "=$C12)*('" & [Link]
& "'!E$13:E$" & SourceLastRow1 & "=$F12))"
End With

'Eliminate Duplicate Rows


Dim MyRange As Range

LastRow = [Link]("F" & [Link]).End(xlUp).Row


Set MyRange = [Link]("A11:AE" & LastRow)
[Link] Columns:=Array(2, 3, 4, 6), Header:=xlYes
Range("D:D,J:X").[Link] = xlCenter

'Border Around Case Quantity Sum


With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("V12:V" & OutputLastRow).Borders(xlEdgeLeft).Weight = xlMedium
.Range("V12:V" & OutputLastRow).Borders(xlEdgeRight).Weight = xlMedium
End With

'Border Separating Old from New


With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("X12:X" & OutputLastRow).Borders(xlEdgeLeft).Weight = xlThick
End With

'New Product Information

'FORMAT "FROM TO" SHEET AS TEXT


Dim rng As Range: Set rng = [Link]("A1:H1000").CurrentRegion
Dim cCell As Range
For Each cCell In [Link]
If IsNumeric([Link]) Then
[Link] = "'" & CStr([Link])
End If
Next cCell

'FromTo Product Number


With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("Z12:Z" & OutputLastRow).Formula = _
"=IFERROR(INDEX('" & [Link] & "'!F$3:F$" & SourceLastRow2 & ",MATCH(1,INDEX(($F
12='" & [Link] & "'!A$3:A$" & SourceLastRow2 & ")*($I12='" & [Link] & "'!D$3:D$"
& SourceLastRow2 & "),0,1),0),1),INDEX('" & [Link] & "'!F$3:F$" & SourceLastRow2 & ",MATCH
($F12,'" & [Link] & "'!F$3:F$" & SourceLastRow2 & ",0)))"
End With
Module1 - 5

'FromTo Product Description


With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("AA12:AA" & OutputLastRow).Formula = _
"=IFERROR(VLOOKUP(F12,'" & [Link] & "'!$A$3:$I$" & SourceLastRow2 & ",7,FALSE),
INDEX('" & [Link] & "'!$G$3:$G$" & SourceLastRow2 & ",MATCH($F12,'" & [Link] & "
'!$F$3:$F$" & SourceLastRow2 & ",0)))"
End With

'FromTo Brand ID
With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("AC12:AC" & OutputLastRow).Formula = _
"=VLOOKUP(Z12,'" & [Link] & "'!$F$3:$I$" & SourceLastRow2 & ",3,FALSE)"
End With

'Proprietary Status
With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("AE12:AE" & OutputLastRow).Formula = _
"=VLOOKUP(F12,'" & [Link] & "'!$C$2:$K$" & SourceLastRow3 & ",9,FALSE)"
End With

'Sector
With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("X12:X" & OutputLastRow).Formula = _
"=IF($D12="""","""",$D12)"
End With

'Order Guide
With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("Y12:Y" & OutputLastRow).Formula = _
"=IF($E12="""","""",$E12)"
End With

'Stocking Status
With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("AD12:AD" & OutputLastRow).Formula = _
"=INDEX('" & [Link] & "'!I$2:I$" & SourceLastRow4 & ",MATCH(1,INDEX((A12='" & sourc
[Link] & "'!A$2:A$" & SourceLastRow4 & ")*(Z12='" & [Link] & "'!B$2:B$" & SourceLastR
ow4 & "),0,1),0),1)"
End With

'Pack / Size
With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("AB12:AB" & OutputLastRow).Formula = _
"=IFERROR(VLOOKUP(Z12,'" & [Link] & "'!$E$3:$G$" & SourceLastRow1 & ",3,FALSE),""""
)"
End With

'Format Entire Worksheet as "General"


[Link] = "General"

'Calculation Formulas
'Sum Case Quantity
With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
.Range("V12:V" & OutputLastRow).Formula = _
"=IF(SUM($J12:$U12)="""","""",SUM($J12:$U12))"
End With

'Average Case Quantity


With outputSheet
OutputLastRow = .Cells(.[Link], "F").End(xlUp).Row
Module1 - 6

.Range("W12:W" & OutputLastRow).Formula = _


"=IF($V12/'" & [Link] & "'!$K$2>1,$V12/'" & [Link] & "'!$K$2,""Less
Than 1"")"
.Range("W12:W" & OutputLastRow).NumberFormat = "####"
'IF($V12/'" & [Link] & "'!$K$2>1,$V12/'" & [Link] & "'!$K$2,"Less Th
an 1")
End With

End Sub

Common questions

Powered by AI

The script uses a repeated SUMPRODUCT formula for each month, adjusting the criteria that matches the respective month's indicative cell (e.g., $J$10 for January, $K$10 for February) to calculate separate sums for each month from January to December. This approach employs criteria within the indexes to dynamically change the included range and conditions for each calculation, ensuring monthly data is calculated separately using the appropriate data filters on varying columns and criteria .

The script applies a general number formatting across an entire worksheet using the command 'ActiveSheet.Cells.NumberFormat = "General"'. This format is specified to ensure that all cells use a standard numerical format, which prevents unintended data type changes (e.g., automatic conversion to dates) and maintains the data's numerical integrity during calculations and presentations .

The primary purpose of the IFERROR function combined with INDEX and MATCH is to provide a fallback value in case an error occurs during the lookup operation. In the 'FromTo Product Number' formula, IFERROR catches errors (such as when there's no match found with INDEX-MATCH) and provides an alternate result by switching to a secondary MATCH attempt with a different reference column, ensuring the operation returns a valid result or nothing instead of an error .

The script converts numeric data stored as text by checking if the cell value is numeric using the IsNumeric function and then explicitly converting it to a text format by prepending a single quote and using CStr for conversion. It iterates through all cells in the designated range of the 'FromTo' sheet, ensuring consistent data format. This conversion prevents potential errors in numerical operations and comparisons, maintaining data integrity throughout the processing workflow .

The script handles duplicate entries by using the RemoveDuplicates method on a specified range of cells, specifically targeting columns 2, 3, 4, and 6. This method checks the specified columns for duplicate entries and removes any duplicate rows, ensuring that each row in the output is unique based on the values in those columns .

The script uses multi-level conditional formulas employing nested IF statements. It cross-references multiple guiding values ($BA$12 to $BA$17 and corresponding fields) to form a logical chain that fills in the 'Order Guide Type' based on prior associations made in the cleaning process. This ensures that if certain match conditions are met, the relevant data sections are populated with the correct corresponding guide type value, otherwise default values are set to ensure data completeness and accuracy .

The script uses the 'End(xlUp).Row' method to find the last row in a particular column by moving upwards from the bottom of the worksheet to find the last non-empty cell. Similarly, it uses 'End(xlUp).Column' to determine the last column from the bottom of a column. These methods are used for each source sheet, such as 'sourceSheet', 'sourceSheet1', etc., determining the last row or column for subsequent data manipulation .

To ensure consistent horizontal alignment within the processed dataset, the script applies 'HorizontalAlignment' to the entire range within specific columns, centering the data. It targets columns D to D and J to X for this operation, ensuring that data presentation within these columns is visually uniform and aligned in the center, improving readability and consistency in appearance .

The SUBSTITUTE function in the formula is used to remove the substring 'SYSCO ' from items in column M of the iTrade Report sheet. This step is important for standardizing the data by eliminating the prefix, likely serving a purpose of data cleaning and ensuring consistent naming conventions for further processing within the spreadsheet .

The script utilizes complex conditional formulas across multiple parts to populate the CleanUp worksheet. In Part I, it uses an IFERROR and INDEX-MATCH formula to search for matches in a given range and return a corresponding value, thus categorizing items based on their matched index in the given sector range [$AZ$12:$AZ$17]. In Part II, further conditional logic is applied using nested IF statements that specify values for different conditions set by the sector matches. These multi-step procedures allow the script to categorize and populate rows dynamically based on the extracted and processed data .

You might also like