Code:
Sub DkRateVarianceMacro()
Dim myBook As Workbook
Dim CADsheet, Summarysheet, DKData As Worksheet
Dim myRow, myCol, DKVar, myRange As Long
Dim mySupRange As Long
' Setting Active Workbook
Set myBook = ActiveWorkbook
Set CADsheet = myBook.Sheets("CAD")
Set Summarysheet = myBook.Sheets("CurrenciesAndDeviantSuppliers")
' Pulls the Supplier names from the DK Rate Sheet
Sheets.Add.Name = "DKRateVariances"
Set DKData = ActiveSheet
myRow = 4
myCol = 4
Do Until CADsheet.Cells(2, myCol) = ""
DKData.Cells(myRow, 1) = CADsheet.Cells(2, myCol)
myCol = myCol + 1
myRow = myRow + 1
' Pulls the Currency labels from DK Rate Sheet
Loop
myRow = 5
myCol = 2
Do Until myBook.Sheets("Summary").Cells(myRow, 1) = ""
DKData.Cells(2, myCol) = myBook.Sheets("Summary").Cells(myRow, 1)
DKData.Cells(3, myCol) = myBook.Sheets("Summary").Cells(myRow, 7)
DKData.Cells(1, myCol) = myBook.Sheets("Summary").Cells(myRow, 2)
myRow = myRow + 1
myCol = myCol + 1
Loop
myRow = 4
myCol = 2
Do Until DKData.Cells(2, myCol) = ""
Summarysheet.Activate
myRange = Cells.Find(What:=DKData.Cells(1, myCol), After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).row
DKVar = 4
Do Until Summarysheet.Cells(myRange, DKVar) = ""
DKData.Activate
mySupRange = Cells.Find(What:=Summarysheet.Cells(myRange, DKVar), After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).row
If (Summarysheet.Cells(myRange + 4, DKVar) - Summarysheet.Cells(myRange + 4, 2)) / Summarysheet.Cells(myRange + 4, 2) <> 0 Then
DKData.Cells(mySupRange, myCol) = (Summarysheet.Cells(myRange + 4, DKVar) - Summarysheet.Cells(myRange + 4, 2)) / Summarysheet.Cells(myRange + 4, 2)
DKData.Cells(mySupRange, myCol).NumberFormat = "0.00%"
End If
DKVar = DKVar + 1
Loop
DKData.Cells(1, myCol) = ""
myRow = myRow + 1
myCol = myCol + 1
Loop
' Formatting Cells
DKData.Cells(1, 1) = "DK Rate Variance Percentage By Supplier"
DKData.Cells(3, 1) = "Digikey Sales Rate"
DKData.Cells(1, 1).Interior.ColorIndex = 15
DKData.Range("B3:P3").Interior.ColorIndex = 15
DKData.Cells(1, 1).Font.Bold = True
DKData.Range("A3:A86").Font.Bold = True
DKData.Range("B2:P2").Font.Bold = True
DKData.Range("B3:P3").Font.Bold = True
DKData.Range("B2:P2").Interior.ColorIndex = 15
DKData.Range("A1:P1").Merge
DKData.Range("A3:P86").Borders.LineStyle = xlContinuous
DKData.Cells(1, 1).HorizontalAlignment = xlCenter
DKData.Range("A2:A86").Interior.ColorIndex = 15
DKData.Range("A:P").Columns.AutoFit
End Sub