Waste

Sub Waste()
'
' Waste Macro
'
' Keyboard Shortcut: Ctrl+Shift+W
'



Dim wb As Workbook
Set wb = ActiveWorkbook
Dim ws As Worksheet

Application.DisplayAlerts = False
'Copy and paste to previous week waste
Worksheets("Waste").Activate
Cells.Select
    Selection.Copy
    Sheets("Previous week waste").Select
    Cells.Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


'Import the sames and waste reports as worksheets
Set ws = wb.Sheets.Add(Type:="C:\Users\Sam4WinUser\Desktop\Robert\FV wastage\waste.Xls", After:=Application.ActiveSheet)
ws.Name = "waste SAM"

Set ws = wb.Sheets.Add(Type:="C:\Users\Sam4WinUser\Desktop\Robert\FV wastage\sales.Xls", After:=Application.ActiveSheet)
ws.Name = "sales SAM"

'Delete empty columns in the SAM generated sales and waste reports
Sheets("sales SAM").Range("B:B,D:D,F:F,H:I,K:K,N:N,Q:Q,S:S,V:V").EntireColumn.Delete
Sheets("waste SAM").Range("B:D,F:I,K:K,M:P,S:S").EntireColumn.Delete

'Copy the sales SAM worksheet to the sales sheet, then delete the sales SAM
Worksheets("sales SAM").Activate

Range("C1").Select
Selection.End(xlDown).Select
Selection.Offset(2, 0).Select
ActiveCell.Value = "sub total"

Cells.Select
Selection.Copy
Sheets("sales").Select
Cells.Select
ActiveSheet.Paste

Worksheets("sales SAM").Delete
'Done and dusted

'Check and match the row numbers in the old and new waste work sheeets
Worksheets("Waste").Activate
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
oldNumRows = Selection.Rows.Count

Worksheets("waste SAM").Activate
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
newNumRows = Selection.Rows.Count - 1

Worksheets("Waste").Activate
If oldNumRows < newNumRows Then
    difference = newNumRows - oldNumRows
    Rows("28:" & difference + 27).Insert Shift:=xlDown, _
      CopyOrigin:=xlFormatFromLeftOrBelow
    End If

If oldNumRows > newNumRows Then
    difference = oldNumRows - newNumRows
    Rows("28:" & difference + 27).Delete
    End If
'Matched

'Copy and paste info from the waste SAM sheet to the Waste sheet and delete waste SAM sheet
Worksheets("waste SAM").Activate
Range("A2:F" & newNumRows + 1).Select
Selection.Copy
Worksheets("Waste").Activate
Range("A3").PasteSpecial Paste:=xlPasteValues

Worksheets("waste SAM").Activate
Range("G2:I" & newNumRows + 1).Select
Selection.Copy
Worksheets("Waste").Activate
Range("I3").PasteSpecial Paste:=xlPasteValues
'Done and dusted :)

'Reassign ranges
Range("L3:O3").AutoFill Destination:=Range("L3:O" & newNumRows + 2)

Worksheets("waste SAM").Delete


'Sort things
Range("F3").Select
    Selection.Sort Key1:=Range("F2:F" & newNumRows + 1), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

'Assign order numbers
Range("G3").Value = 1
Range("G4").Value = 2
Range("G3:G4").AutoFill Destination:=Range("G3:G" & newNumRows + 2)

'Clear the comments area
 Range("P3:P" & newNumRows + 2).Select
    Selection.ClearContents

'Copy the group margin sales table
Range("Q3:T20").Select
Selection.Copy
Worksheets("Sheet1").Activate
Range("A4").PasteSpecial Paste:=xlPasteValues
Worksheets("Waste").Activate

'Check the group products



'Fonts and borders
 With Selection.Font
        .Name = "Calibri"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With

    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    currentLastRow = Selection.Rows.Count + 2
    Dim i As Integer
    Worksheets("Waste").Activate
    For i = 3 To currentLastRow
        If InStr(Cells(i, 12).Text, "#N/A") Then
            barcode = Cells(i, 3).Value
            Worksheets("Sheet1").Activate
            Set Rng = Range("G2:X23")
            For Each cell In Rng
                If cell.Value = barcode Then
                    cell.Select
                    groupCell = Cells(1, Selection.Column).Value
                    For j = 4 To 21
                        If Cells(j, 1).Value = groupCell Then
                            Worksheets("Sheet1").Range(Cells(j, 3), Cells(j, 4)).Copy
                            Worksheets("Waste").Activate
                            Range(Cells(i, 12), Cells(i, 13)).PasteSpecial Paste:=xlPasteFormats
                            Range(Cells(i, 12), Cells(i, 13)).PasteSpecial Paste:=xlPasteFormulas
                        End If
                    Next j
                End If
            Next cell
        End If
        Worksheets("Waste").Activate
Next i

Range("N" & newNumRows + 2).Select

Application.DisplayAlerts = True

End Sub