Procedure Too Big

W

wayneshirley

Board Regular
Joined
Jun 23, 2003
Messages
140
Hi there,

I am getting an error message "procedure too big." My macro is in Excel 1997-2003 format.

I have commented out as much unnecessary code as I can find.

Is there anything else i can do?

Thank you.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
How many lines of code do you have?

Have you considered splitting it down to small processes and then use additional sub routines to call them individually?
 
Upvote 0
Hi Trevor,

The macro formats 1 report that contains 12 worksheets. The ease with which the macro has been able to format this report is great.

I am reluctant to make this into a multi step process as it would make it much less convienent and user friendly.

Is Excel 2007 capable of handling a bigger procedure?

Thank you.
 
Upvote 0
Unless so you show your code it is hard to advice.

Are you using an Array option for the 12 worksheets or are you using i = 1 to 12, and what formats etc are being applied?

Office 2007 allows you to use different type of saved workbooks, so you end up with different file extensions

xlsx normal workbook
xlsm macro enabled
xlsb binary workbook

and others

You will have to consider what your code is doing in 2003 before you consider 2007, as things like FileSearch has been removed in 2007 so you have to look at Dir
 
Upvote 0
Hi Trevor,

This is a summary of what I am doing:

1) Formatting on "Original Data" sheet.
2) Create new sheet titled "Export" and format.
3) Use "Export" sheet as a template & copy/create 10 more sheets.
4) Copy data from "Original Data" sheet & split up amoungst the other 11 sheets.
5) More formatting on all sheets.

I hope this summary & the code below is enough to work out a solution. Really appreciate your time and help.

The code is as follows:

Code
Sub MacroTBEOM()
'
' MacroTB Macro
' Macro recorded 17/05/2007 by Wesfarmers Industrial & Safety
'

'
ChDir "C:\Documents and Settings\AWP00\Desktop"
Workbooks.Open Filename:="C:\Documents and Settings\AWP00\Desktop\TB.csv"
Application.ScreenUpdating = False

Rows("1:10").Delete Shift:=xlUp
' Selection.Delete Shift:=xlUp
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With Columns("A:B")
' With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
' .WrapText = False
' .Orientation = 0
' .AddIndent = False
' .IndentLevel = 0
' .ShrinkToFit = False
.ReadingOrder = xlContext
' .MergeCells = False
End With
Rows("1:1").Font.Bold = True
Columns("C:C").ColumnWidth = 25.43
Columns("A:A").ColumnWidth = 5.57

Columns("E:E").Delete Shift:=xlToLeft



Range("D1:N1").Value = Array("Terms", "Total", "Current", "Jan-30", "31-60 Days", "61-90 Days", "91-120 Days", "121-150 Days", "151+ Days", "Over 30", "Notes")


Columns("E:M").NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
' Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "

Columns("N:N").Select
Selection.InsertIndent 1
Range("C1").Select
ActiveCell.FormulaR1C1 = "Customer Name"
' Range("E1").Select
' ActiveCell.FormulaR1C1 = "Total"


' Right align E1 to M1
' --------------------

With Range("E1:M1")
' With Selection
.HorizontalAlignment = xlRight
' .VerticalAlignment = xlBottom
' .WrapText = False
' .Orientation = 0
' .AddIndent = False
' .IndentLevel = 0
' .ShrinkToFit = False
.ReadingOrder = xlContext
' .MergeCells = False
End With

ActiveWindow.DisplayZeros = False

Range("F1").Select
ActiveCell.FormulaR1C1 = "1-30 day"
Range("A2").Select
ActiveWindow.FreezePanes = True

Sheets("TB").Name = "Original Data"

' CREATE NEW SHEET NAMED EXPORT AND FORMAT
' =========================================

Sheets.Add
ActiveSheet.Name = "Export"

Sheets("Original Data").Select
Range("A1:N1").Select
With Selection.Interior
.ColorIndex = 15
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
End With



Range("A1:N1").Copy
Sheets("Export").Select
ActiveSheet.Paste
Range("A2").Select
Sheets("Original Data").Select

ActiveSheet.Paste
Application.CutCopyMode = False
' Range("A2").Select
Range("M2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Range("M2").Select

' DELETE ROWS WITH JUNK DATA IN COLUMNS A AND E
' =============================================


' Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
With Range("E" & i)
If Not IsNumeric(.Value) Then .EntireRow.Delete
End With
Next i

' Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
With Range("A" & i)
If Not IsNumeric(.Value) Then .EntireRow.Delete
End With
Next i




' FILL DOWN FORMULA IN OVER 30 DAYS COLUMN
' ========================================


' New fill down formula
' ---------------------
Range("M2").Formula = "=Sum(H2:L2)"
Range("M2").Copy Range("M2:M" & Cells(Rows.Count, 11).End(xlUp).Row - 1)




Range("A2").Select
Sheets("Export").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
' .PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
' .LeftHeader = ""
.CenterHeader = "&""Arial,Bold""Export Trial Balance"
' .RightHeader = ""
.LeftFooter = "Printed &D"
' .CenterFooter = ""
.RightFooter = "&P"
.LeftMargin = Application.InchesToPoints(0.354330708661417)
.RightMargin = Application.InchesToPoints(0.354330708661417)
.TopMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.984251968503937)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
' .PrintHeadings = False
.PrintGridlines = True
' .PrintComments = xlPrintNoComments
' .PrintQuality = 600
.CenterHorizontally = True
' .CenterVertically = False
.Orientation = xlLandscape
' .Draft = False
' .PaperSize = xlPaperLetter
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = 100
' .PrintErrors = xlPrintErrorsDisplayed
End With
Sheets("Original Data").Select
With ActiveSheet.PageSetup
' .PrintTitleRows = ""
' .PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
' .LeftHeader = ""
.CenterHeader = "&""Arial,Bold""Trial Balance - Original Data"
' .RightHeader = ""
.LeftFooter = "Printed &D"
' .CenterFooter = ""
.RightFooter = "&P"
.LeftMargin = Application.InchesToPoints(0.748031496062992)
.RightMargin = Application.InchesToPoints(0.748031496062992)
.TopMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.984251968503937)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
' .PrintHeadings = False
.PrintGridlines = True
' .PrintComments = xlPrintNoComments
' .PrintQuality = 600
.CenterHorizontally = True
' .CenterVertically = False
.Orientation = xlLandscape
' .Draft = False
' .PaperSize = xlPaperLetter
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = 100
' .PrintErrors = xlPrintErrorsDisplayed
End With
Range("A3").Select

Columns("D:D").ColumnWidth = 5.1

Columns("D:D").Select
With Selection
.HorizontalAlignment = xlCenter
' .VerticalAlignment = xlBottom
' .WrapText = False
' .Orientation = 0
' .AddIndent = False
' .IndentLevel = 0
' .ShrinkToFit = False
.ReadingOrder = xlContext
' .MergeCells = False
End With



' END OF GENERAL FORMATTING ON EXPORT SHEET
' =========================================

Columns("B:B").NumberFormat = "000000"
Columns("D:D").Insert Shift:=xlToRight



' PROPER FORMULA ON NAMES AND FILL DOWN
' =====================================

Range("D2").Select
ActiveCell.FormulaR1C1 = "=PROPER(RC[-1])"

ActiveCell.Offset(0, 1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "x"
Selection.End(xlUp).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste

Range("C1").Select
Selection.Cut Destination:=Range("D1")
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("C:C").Delete Shift:=xlToLeft
Columns("C:C").ColumnWidth = 21.29
Columns("D:D").ColumnWidth = 5.1


' PUT MONTHS MS COLUMN HEADINGS
' =============================

Dim lRow As Long 'Row you want information to be on
' Dim lCol As Long
Dim lIncrement As Long
Dim lStart As Long 'Column you want to start in
Dim lEnd As Long 'Column you want to end with

lIncrement = -1 'Starting increment
lRow = 1
lStart = 6
lEnd = 12

' Formats range so the date will appear in the needed format mmm = Jan,
' mmm yy = Jan 07, mmm yyyy = Jan 2007
Range(Cells(lRow, lStart), Cells(lRow, lEnd)).NumberFormat = "mmm"

' This loop fills in the header values base on today's date. Each column is
' advanced based on the incrementation set in lIncrement
For lCol = lStart To lEnd
ActiveSheet.Cells(lRow, lCol).Value = DateAdd("m", lIncrement, Date)
lIncrement = lIncrement - 1
Next lCol

' FORMATTING ON EXPORT SHEET
' ==========================


Sheets("Export").Select
Columns("C:C").ColumnWidth = 21.29
Columns("E:E").ColumnWidth = 12.57
Columns("D:D").ColumnWidth = 5.14
Columns("F:H").ColumnWidth = 11.43
Columns("I:L").ColumnWidth = 10.57
Columns("M:M").ColumnWidth = 12.71
Columns("N:N").ColumnWidth = 40.57

With Range("D1:K1")
' With Selection
.HorizontalAlignment = xlRight
' .VerticalAlignment = xlBottom
' .WrapText = False
' .Orientation = 0
' .AddIndent = False
' .IndentLevel = 0
' .ShrinkToFit = False
.ReadingOrder = xlContext
' .MergeCells = False
End With

Columns("D:D").Select
With Selection
.HorizontalAlignment = xlCenter
' .VerticalAlignment = xlBottom
' .WrapText = False
' .Orientation = 0
' .AddIndent = False
' .IndentLevel = 0
' .ShrinkToFit = False
.ReadingOrder = xlContext
' .MergeCells = False
End With

Columns("A:A").EntireColumn.Hidden = True

Columns("E:M").NumberFormat = "#,##0.00"

Range("B2").Select
ActiveWindow.DisplayZeros = False
ActiveWindow.FreezePanes = True

' COPY HEADINGS FROM ORIGINAL DATA TO EXPORT
' ==========================================

Sheets("Export").Select
Range("E1:K1").NumberFormat = "@"


Sheets("Original Data").Select
Range("D1:N1").Select
Selection.Copy
Sheets("Export").Select
Range("D1").Select
ActiveSheet.Paste
Range("A2").Select
Application.CutCopyMode = False




' ADD EXTRA SHEETS FOR EACH LEDGER
' ================================
' These sheets are copied from Export tab so that formatting is correct.

Sheets("Export").Copy After:=Sheets(2)
Sheets("Export (2)").Copy After:=Sheets(3)
Sheets("Export (3)").Copy After:=Sheets(4)
Sheets("Export (4)").Copy After:=Sheets(5)
Sheets("Export (5)").Copy After:=Sheets(6)
Sheets("Export (6)").Copy After:=Sheets(7)
Sheets("Export (7)").Copy After:=Sheets(8)
Sheets("Export (8)").Copy After:=Sheets(9)
Sheets("Export (9)").Copy After:=Sheets(10)
Sheets("Export (10)").Copy After:=Sheets(11)
Sheets("Export (11)").Copy After:=Sheets(12)


Sheets("Export (2)").Name = "Spare"
Sheets("Export (3)").Name = "VL"
Sheets("Export (4)").Name = "DW"
Sheets("Export (5)").Name = "HH"
Sheets("Export (6)").Name = "HS"
Sheets("Export (7)").Name = "ML"
Sheets("Export (8)").Name = "MGMT"
Sheets("Export (9)").Name = "MS"
Sheets("Export (10)").Name = "RC"
Sheets("Export (11)").Name = "PG"
Sheets("Export (12)").Name = "CM"


' RENAME EACH SHEET
' =================

Sheets("Spare").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
' .PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
' .LeftHeader = ""
.CenterHeader = "&""Arial,Bold""Trial Balance Spare (8067 7201)"
' .RightHeader = ""
' .LeftFooter = ""
' .CenterFooter = ""
' .RightFooter = ""
' .LeftMargin = Application.InchesToPoints(0.75)
' .RightMargin = Application.InchesToPoints(0.75)
' .TopMargin = Application.InchesToPoints(1)
' .BottomMargin = Application.InchesToPoints(1)
' .HeaderMargin = Application.InchesToPoints(0.5)
' .FooterMargin = Application.InchesToPoints(0.5)
' .PrintHeadings = False
' .PrintGridlines = False
' .PrintComments = xlPrintNoComments
' .PrintQuality = 600
' .CenterHorizontally = False
' .CenterVertically = False
' .Orientation = xlPortrait
' .Draft = False
' .PaperSize = xlPaperA4
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = 100
' .PrintErrors = xlPrintErrorsDisplayed
End With

Sheets("PG").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
' .PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
' .LeftHeader = ""
.CenterHeader = "&""Arial,Bold""Trial Balance Pauline (8110 7202)"
' .RightHeader = ""
' .LeftFooter = ""
' .CenterFooter = ""
' .RightFooter = ""
' .LeftMargin = Application.InchesToPoints(0.75)
' .RightMargin = Application.InchesToPoints(0.75)
' .TopMargin = Application.InchesToPoints(1)
' .BottomMargin = Application.InchesToPoints(1)
' .HeaderMargin = Application.InchesToPoints(0.5)
' .FooterMargin = Application.InchesToPoints(0.5)
' .PrintHeadings = False
' .PrintGridlines = False
' .PrintComments = xlPrintNoComments
' .PrintQuality = 600
' .CenterHorizontally = False
' .CenterVertically = False
' .Orientation = xlPortrait
' .Draft = False
' .PaperSize = xlPaperA4
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = 100
' .PrintErrors = xlPrintErrorsDisplayed
End With

Sheets("DW").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
' .PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
' .LeftHeader = ""
.CenterHeader = "&""Arial,Bold""Trial Balance Danielle (8113 7203)"
' .RightHeader = ""
' .LeftFooter = ""
' .CenterFooter = ""
' .RightFooter = ""
' .LeftMargin = Application.InchesToPoints(0.75)
' .RightMargin = Application.InchesToPoints(0.75)
' .TopMargin = Application.InchesToPoints(1)
' .BottomMargin = Application.InchesToPoints(1)
' .HeaderMargin = Application.InchesToPoints(0.5)
' .FooterMargin = Application.InchesToPoints(0.5)
' .PrintHeadings = False
' .PrintGridlines = False
' .PrintComments = xlPrintNoComments
' .PrintQuality = 600
' .CenterHorizontally = False
' .CenterVertically = False
' .Orientation = xlPortrait
' .Draft = False
' .PaperSize = xlPaperA4
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = 100
' .PrintErrors = xlPrintErrorsDisplayed
End With

Sheets("HH").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
' .PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
' .LeftHeader = ""
.CenterHeader = "Trial Balance Heidi (8116 7204)"
' .RightHeader = ""
' .LeftFooter = ""
' .CenterFooter = ""
' .RightFooter = ""
' .LeftMargin = Application.InchesToPoints(0.75)
' .RightMargin = Application.InchesToPoints(0.75)
' .TopMargin = Application.InchesToPoints(1)
' .BottomMargin = Application.InchesToPoints(1)
' .HeaderMargin = Application.InchesToPoints(0.5)
' .FooterMargin = Application.InchesToPoints(0.5)
' .PrintHeadings = False
' .PrintGridlines = False
' .PrintComments = xlPrintNoComments
' .PrintQuality = 600
' .CenterHorizontally = False
' .CenterVertically = False
' .Orientation = xlPortrait
' .Draft = False
' .PaperSize = xlPaperA4
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = 100
' .PrintErrors = xlPrintErrorsDisplayed
End With

Sheets("HS").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
' .PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
' .LeftHeader = ""
.CenterHeader = "&""Arial,Bold""Trial Balance Harbans (8068 7065)"
' .RightHeader = ""
' .LeftFooter = ""
' .CenterFooter = ""
' .RightFooter = ""
' .LeftMargin = Application.InchesToPoints(0.75)
' .RightMargin = Application.InchesToPoints(0.75)
' .TopMargin = Application.InchesToPoints(1)
' .BottomMargin = Application.InchesToPoints(1)
' .HeaderMargin = Application.InchesToPoints(0.5)
' .FooterMargin = Application.InchesToPoints(0.5)
' .PrintHeadings = False
' .PrintGridlines = False
' .PrintComments = xlPrintNoComments
' .PrintQuality = 600
' .CenterHorizontally = False
' .CenterVertically = False
' .Orientation = xlPortrait
' .Draft = False
' .PaperSize = xlPaperA4
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = 100
' .PrintErrors = xlPrintErrorsDisplayed
End With

Sheets("ML").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
' .PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
' .LeftHeader = ""
.CenterHeader = "&""Arial,Bold""Trial Balance Marme (8112 7221)"
' .RightHeader = ""
' .LeftFooter = ""
' .CenterFooter = ""
' .RightFooter = ""
' .LeftMargin = Application.InchesToPoints(0.75)
' .RightMargin = Application.InchesToPoints(0.75)
' .TopMargin = Application.InchesToPoints(1)
' .BottomMargin = Application.InchesToPoints(1)
' .HeaderMargin = Application.InchesToPoints(0.5)
' .FooterMargin = Application.InchesToPoints(0.5)
' .PrintHeadings = False
' .PrintGridlines = False
' .PrintComments = xlPrintNoComments
' .PrintQuality = 600
' .CenterHorizontally = False
' .CenterVertically = False
' .Orientation = xlPortrait
' .Draft = False
' .PaperSize = xlPaperA4
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = 100
' .PrintErrors = xlPrintErrorsDisplayed
End With

Sheets("MGMT").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
' .PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
' .LeftHeader = ""
.CenterHeader = "&""Arial,Bold""Trial Balance Management Accounts"
' .RightHeader = ""
' .LeftFooter = ""
' .CenterFooter = ""
' .RightFooter = ""
' .LeftMargin = Application.InchesToPoints(0.75)
' .RightMargin = Application.InchesToPoints(0.75)
' .TopMargin = Application.InchesToPoints(1)
' .BottomMargin = Application.InchesToPoints(1)
' .HeaderMargin = Application.InchesToPoints(0.5)
' .FooterMargin = Application.InchesToPoints(0.5)
' .PrintHeadings = False
' .PrintGridlines = False
' .PrintComments = xlPrintNoComments
' .PrintQuality = 600
' .CenterHorizontally = False
' .CenterVertically = False
' .Orientation = xlPortrait
' .Draft = False
' .PaperSize = xlPaperA4
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = 100
' .PrintErrors = xlPrintErrorsDisplayed
End With

Sheets("MS").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
' .PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
' .LeftHeader = ""
.CenterHeader = "&""Arial,Bold""Trial Balance Mandeep (8201 7220)"
' .RightHeader = ""
' .LeftFooter = ""
' .CenterFooter = ""
' .RightFooter = ""
' .LeftMargin = Application.InchesToPoints(0.75)
' .RightMargin = Application.InchesToPoints(0.75)
' .TopMargin = Application.InchesToPoints(1)
' .BottomMargin = Application.InchesToPoints(1)
' .HeaderMargin = Application.InchesToPoints(0.5)
' .FooterMargin = Application.InchesToPoints(0.5)
' .PrintHeadings = False
' .PrintGridlines = False
' .PrintComments = xlPrintNoComments
' .PrintQuality = 600
' .CenterHorizontally = False
' .CenterVertically = False
' .Orientation = xlPortrait
' .Draft = False
' .PaperSize = xlPaperA4
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = 100
' .PrintErrors = xlPrintErrorsDisplayed
End With
Sheets("RC").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
' .PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
' .LeftHeader = ""
.CenterHeader = "&""Arial,Bold""Trial Balance Rosemary (8202 7222 Buying Group Members, 8204 7223 The Rest)"
' .RightHeader = ""
' .LeftFooter = ""
' .CenterFooter = ""
' .RightFooter = ""
' .LeftMargin = Application.InchesToPoints(0.75)
' .RightMargin = Application.InchesToPoints(0.75)
' .TopMargin = Application.InchesToPoints(1)
' .BottomMargin = Application.InchesToPoints(1)
' .HeaderMargin = Application.InchesToPoints(0.5)
' .FooterMargin = Application.InchesToPoints(0.5)
' .PrintHeadings = False
' .PrintGridlines = False
' .PrintComments = xlPrintNoComments
' .PrintQuality = 600
' .CenterHorizontally = False
' .CenterVertically = False
' .Orientation = xlPortrait
' .Draft = False
' .PaperSize = xlPaperA4
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = 100
' .PrintErrors = xlPrintErrorsDisplayed
End With

Sheets("VL").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
' .PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
' .LeftHeader = ""
.CenterHeader = "&""Arial,Bold""Trial Balance Vannessa (8203 7066)"
' .RightHeader = ""
' .LeftFooter = ""
' .CenterFooter = ""
' .RightFooter = ""
' .LeftMargin = Application.InchesToPoints(0.75)
' .RightMargin = Application.InchesToPoints(0.75)
' .TopMargin = Application.InchesToPoints(1)
' .BottomMargin = Application.InchesToPoints(1)
' .HeaderMargin = Application.InchesToPoints(0.5)
' .FooterMargin = Application.InchesToPoints(0.5)
' .PrintHeadings = False
' .PrintGridlines = False
' .PrintComments = xlPrintNoComments
' .PrintQuality = 600
' .CenterHorizontally = False
' .CenterVertically = False
' .Orientation = xlPortrait
' .Draft = False
' .PaperSize = xlPaperA4
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = 100
' .PrintErrors = xlPrintErrorsDisplayed
End With

Sheets("CM").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
' .PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
' .LeftHeader = ""
.CenterHeader = "&""Arial,Bold""Trial Balance Carissa (7067 8108)"
' .RightHeader = ""
' .LeftFooter = ""
' .CenterFooter = ""
' .RightFooter = ""
' .LeftMargin = Application.InchesToPoints(0.75)
' .RightMargin = Application.InchesToPoints(0.75)
' .TopMargin = Application.InchesToPoints(1)
' .BottomMargin = Application.InchesToPoints(1)
' .HeaderMargin = Application.InchesToPoints(0.5)
' .FooterMargin = Application.InchesToPoints(0.5)
' .PrintHeadings = False
' .PrintGridlines = False
' .PrintComments = xlPrintNoComments
' .PrintQuality = 600
' .CenterHorizontally = False
' .CenterVertically = False
' .Orientation = xlPortrait
' .Draft = False
' .PaperSize = xlPaperA4
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = 100
' .PrintErrors = xlPrintErrorsDisplayed
End With
'




' COPY & PASTE 7064 & 8064 TO EXPORT SHEET
' ========================================

Dim wsSht1 As Worksheet
Dim wssht2 As Worksheet
' Dim lRow As Long
Dim lRowSht2 As Long
Dim llastrow As Long
Dim sCustCode As String

Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("Export")

llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2

For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "7064" Or sCustCode = "8064" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow





' TOTAL AT THE BOTTOM OF EXPORT SHEET IN BOLD
' ===========================================

Sheets("Export").Select

Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B2").Select




' PAGE SET UP FORMATING
' =====================

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
' .PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
' .LeftHeader = ""
.CenterHeader = "&""Arial,Bold""Trial Balance - Export"
' .RightHeader = ""
.LeftFooter = "Printed &D"
' .CenterFooter = ""
.RightFooter = "&P"
.LeftMargin = Application.InchesToPoints(0.354330708661417)
.RightMargin = Application.InchesToPoints(0.354330708661417)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
' .PrintHeadings = False
.PrintGridlines = True
' .PrintComments = xlPrintNoComments
' .PrintQuality = 600
.CenterHorizontally = True
' .CenterVertically = False
.Orientation = xlLandscape
' .Draft = False
' .PaperSize = xlPaperLetter
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = 100
' .PrintErrors = xlPrintErrorsDisplayed
End With


' COPY & PASTE 8067 & 7201 TO Spare SHEET
' ====================================

' Dim wsSht1 As Worksheet
' Dim wssht2 As Worksheet
' Dim lRow As Long
' Dim lRowSht2 As Long
' Dim llastrow As Long
' Dim sCustCode As String

Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("Spare")

llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2

For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8067" Or sCustCode = "7201" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow




' PUT TOTALS AT THE BOTTOM OF Spare SHEET IN BOLD
' ============================================

Sheets("Spare").Select

Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
' Range("B2").Select

' COPY & PASTE 8110 & 7202 TO PG SHEET
' ====================================

' Dim wsSht1 As Worksheet
' Dim wssht2 As Worksheet
' Dim lRow As Long
' Dim lRowSht2 As Long
' Dim llastrow As Long
' Dim sCustCode As String

Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("PG")

llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2

For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8110" Or sCustCode = "7202" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow


' PUT TOTALS AT THE BOTTOM OF A SHEET IN BOLD
' ============================================

Sheets("PG").Select

Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
' Range("B2").Select



' COPY & PASTE 8113 & 7203 TO DW SHEET
' ====================================

' Dim wsSht1 As Worksheet
' Dim wssht2 As Worksheet
' Dim lRow As Long
' Dim lRowSht2 As Long
' Dim llastrow As Long
' Dim sCustCode As String

Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("DW")

llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2

For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8113" Or sCustCode = "7203" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow


' PUT TOTALS AT THE BOTTOM OF DW SHEET IN BOLD
' ============================================

Sheets("DW").Select

Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B2").Select

' COPY & PASTE 8116 & 7204 TO HH SHEET
' ====================================

' Dim wsSht1 As Worksheet
' Dim wssht2 As Worksheet
' Dim lRow As Long
' Dim lRowSht2 As Long
' Dim llastrow As Long
' Dim sCustCode As String

Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("HH")

llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2

For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8116" Or sCustCode = "7204" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow


' PUT TOTALS AT THE BOTTOM OF HH SHEET IN BOLD
' ============================================

Sheets("HH").Select

Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
' Range("B2").Select

' COPY & PASTE 8068 & 7065 TO HS SHEET
' ====================================

' Dim wsSht1 As Worksheet
' Dim wssht2 As Worksheet
' Dim lRow As Long
' Dim lRowSht2 As Long
' Dim llastrow As Long
' Dim sCustCode As String

Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("HS")

llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2

For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8068" Or sCustCode = "7065" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow


' PUT TOTALS AT THE BOTTOM OF HS SHEET IN BOLD
' ============================================

Sheets("HS").Select

Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
' Range("B2").Select

' COPY & PASTE 8112 & 7221 TO ML SHEET
' ====================================

' Dim wsSht1 As Worksheet
' Dim wssht2 As Worksheet
' Dim lRow As Long
' Dim lRowSht2 As Long
' Dim llastrow As Long
' Dim sCustCode As String

Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("ML")

llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2

For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8112" Or sCustCode = "7221" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow


' PUT TOTALS AT THE BOTTOM OF ML SHEET IN BOLD
' ============================================

Sheets("ML").Select

Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
' Range("B2").Select

' COPY & PASTE 8900 & 7998 TO MGMT SHEET
' ======================================

' Dim wsSht1 As Worksheet
' Dim wssht2 As Worksheet
' Dim lRow As Long
' Dim lRowSht2 As Long
' Dim llastrow As Long
' Dim sCustCode As String

Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("MGMT")

llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2

For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8900" Or sCustCode = "7998" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow


' PUT TOTALS AT THE BOTTOM OF MGMT SHEET IN BOLD
' ==============================================

Sheets("MGMT").Select

Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("L1").Select
Range("L65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B2").Select


' COPY & PASTE 8201 & 7220 TO MS SHEET
' ====================================

' Dim wsSht1 As Worksheet
' Dim wssht2 As Worksheet
' Dim lRow As Long
' Dim lRowSht2 As Long
' Dim llastrow As Long
' Dim sCustCode As String

Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("MS")

llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2

For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8201" Or sCustCode = "7220" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow


' PUT TOTALS AT THE BOTTOM OF MS SHEET IN BOLD
' ============================================

Sheets("MS").Select

Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B2").Select


' COPY & PASTE 8202, 7222, 8204 & 7223 TO RC SHEET
' ================================================

' Dim wsSht1 As Worksheet
' Dim wssht2 As Worksheet
' Dim lRow As Long
' Dim lRowSht2 As Long
' Dim llastrow As Long
' Dim sCustCode As String

Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("RC")

llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2

For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8204" Or sCustCode = "7223" Or sCustCode = "7222" Or sCustCode = "8202" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow


' PUT TOTALS AT THE BOTTOM OF RC SHEET IN BOLD
' ============================================

Sheets("RC").Select

Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal

ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1

' Range("B2").Select

' COPY & PASTE 8203 & 7066 TO VL SHEET
' ====================================

' Dim wsSht1 As Worksheet
' Dim wssht2 As Worksheet
' Dim lRow As Long
' Dim lRowSht2 As Long
' Dim llastrow As Long
' Dim sCustCode As String

Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("VL")

llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2

For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8203" Or sCustCode = "7061" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow



' PUT TOTALS AT THE BOTTOM OF VL SHEET IN BOLD
' ============================================

Sheets("VL").Select

Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5

Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal


' COPY & PASTE 8108 & 7067 TO CM SHEET
' ====================================

' Dim wsSht1 As Worksheet
' Dim wssht2 As Worksheet
' Dim lRow As Long
' Dim lRowSht2 As Long
' Dim llastrow As Long
' Dim sCustCode As String

Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("CM")

llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2

For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8108" Or sCustCode = "7067" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow



' PUT TOTALS AT THE BOTTOM OF CM SHEET IN BOLD
' ============================================

Sheets("CM").Select

Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True

Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B2").Select




' END OF COPYING AND PASTING ACCTS INTO COLLECTORS TABS
' =====================================================


Sheets("Export").Select
Sheets("Export").Move Before:=Sheets(3)
Sheets("Original Data").Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1


' COLOURED TABS
' =============

ActiveWorkbook.Sheets("CM").Tab.ColorIndex = 53
' Sheets("PC").Select
' Range("B2").Select
ActiveWorkbook.Sheets("VL").Tab.ColorIndex = 16
ActiveWorkbook.Sheets("RC").Tab.ColorIndex = 7
ActiveWorkbook.Sheets("MS").Tab.ColorIndex = 44
ActiveWorkbook.Sheets("MGMT").Tab.ColorIndex = 43
ActiveWorkbook.Sheets("ML").Tab.ColorIndex = 5
ActiveWorkbook.Sheets("HS").Tab.ColorIndex = 3
ActiveWorkbook.Sheets("HH").Tab.ColorIndex = 54
ActiveWorkbook.Sheets("DW").Tab.ColorIndex = 46
ActiveWorkbook.Sheets("PG").Tab.ColorIndex = 1
ActiveWorkbook.Sheets("Spare").Tab.ColorIndex = 6
ActiveWorkbook.Sheets("Export").Tab.ColorIndex = 14
ActiveWorkbook.Sheets("Original Data").Tab.ColorIndex = 9

' HEADING COLOURS
' ===============

' Rosemary
' --------

Sheets("RC").Select
Range("A1:N1").Select
With Selection.Interior
.ColorIndex = 50
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
End With

Rows("1:1").Select
Selection.Font.ColorIndex = 13
Range("B2").Select

' Unhide column A

Columns("A:A").ColumnWidth = 6.14


ActiveWindow.FreezePanes = False
Range("D2").Select
ActiveWindow.FreezePanes = True


' Harbans
' -----

Sheets("HS").Select
Range("B1:N1").Select
With Selection.Interior
.ColorIndex = 3
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
End With

Rows("1:1").Select
Selection.Font.ColorIndex = 2
Range("B2").Select

' Marme
' -------

Sheets("ML").Select
Range("B1:N1").Select
Selection.Font.ColorIndex = 5
With Selection.Interior
.ColorIndex = 3
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
End With

ActiveWindow.FreezePanes = False
Range("D2").Select
ActiveWindow.FreezePanes = True


' Spare
' ------

Sheets("Spare").Select
Range("B1:N1").Select
Selection.Interior.ColorIndex = 5
Selection.Font.ColorIndex = 6
Range("B2").Select

' Vannessa
' --------

Sheets("VL").Select
Range("B1:N1").Select
Selection.Interior.ColorIndex = 16
Selection.Font.ColorIndex = 2
Range("B2").Select


' Heidi
' -----

Sheets("HH").Select
Range("B1:N1").Select
Selection.Interior.ColorIndex = 6

ActiveWindow.FreezePanes = False
Range("D2").Select
ActiveWindow.FreezePanes = True

' Pauline
' -------

Sheets("PG").Select
Range("B1:N1").Select
Selection.Font.ColorIndex = 2
With Selection.Interior
.ColorIndex = 1
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
End With
Range("B2").Select

' Sheets("Original Data").Select
' Range("A4").Select

' Carrisa
' -------
Sheets("CM").Select
Range("B1:N1").Select
Selection.Font.ColorIndex = 5
With Selection.Interior
.ColorIndex = 3
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
End With

Range("B2").Select


' Danielle
' --------
Sheets("DW").Select
Range("B1:N1").Select
Selection.Font.ColorIndex = 38
With Selection.Interior
.ColorIndex = 3
' .Pattern = xlSolid
' .PatternColorIndex = xlAutomatic
End With

Range("B2").Select



' Add in an extra paymt date column N

' Sheets("DW").Select
' Range("N1").Select
' With Selection.Interior
' .ColorIndex = 15
'' .Pattern = xlSolid
'' .PatternColorIndex = xlAutomatic
' End With
'' With Selection
'' .HorizontalAlignment = xlRight
'' .VerticalAlignment = xlBottom
'' .WrapText = False
'' .Orientation = 0
'' .AddIndent = False
'' .IndentLevel = 0
'' .ShrinkToFit = False
'' .ReadingOrder = xlContext
'' .MergeCells = False
'' End With
'
'' Selection.NumberFormat = "#,##0.00"
'
' Range("N1").Select
' ActiveCell.FormulaR1C1 = "Pymt Date" & Chr(10) & "DD/MM/YY"
' With ActiveCell.Characters(Start:=1, Length:=10).Font
' .Name = "Arial"
' .FontStyle = "Bold"
' .Size = 10
'' .Strikethrough = False
'' .Superscript = False
'' .Subscript = False
'' .OutlineFont = False
'' .Shadow = False
'' .Underline = xlUnderlineStyleNone
'' .ColorIndex = xlAutomatic
' End With
' With ActiveCell.Characters(Start:=11, Length:=8).Font
' .Name = "Arial"
' .FontStyle = "Regular"
' .Size = 9
'' .Strikethrough = False
'' .Superscript = False
'' .Subscript = False
'' .OutlineFont = False
'' .Shadow = False
'' .Underline = xlUnderlineStyleNone
'' .ColorIndex = xlAutomatic
' End With
'
'
'
'' ActiveCell.FormulaR1C1 = "Pymt Date"
'' With ActiveCell.Characters(Start:=1, Length:=9).Font
'' .Name = "Arial"
'' .FontStyle = "Bold"
'' .Size = 10
''' .Strikethrough = False
''' .Superscript = False
''' .Subscript = False
''' .OutlineFont = False
''' .Shadow = False
''' .Underline = xlUnderlineStyleNone
''' .ColorIndex = xlAutomatic
'' End With
'
' Range("N1").Select
' With Selection
'' .HorizontalAlignment = xlRight
'' .VerticalAlignment = xlBottom
' .WrapText = True
'' .Orientation = 0
'' .AddIndent = False
'' .IndentLevel = 0
'' .ShrinkToFit = False
' .ReadingOrder = xlContext
'' .MergeCells = False
' End With
' Columns("N:N").Select
' With Selection
' .HorizontalAlignment = xlCenter
'' .VerticalAlignment = xlBottom
'' .Orientation = 0
'' .AddIndent = False
'' .IndentLevel = 0
'' .ShrinkToFit = False
' .ReadingOrder = xlContext
'' .MergeCells = False
' End With
' ActiveWindow.ScrollColumn = 4
' ActiveWindow.ScrollColumn = 3
' ActiveWindow.ScrollColumn = 2
' Range("B2").Select

' Conditional formatting to colour cell in column N when past due
' ---------------------------------------------------------------

' LR = Range("A" & Rows.Count).End(xlUp).Row
'With Range("N2:N" & LR).FormatConditions
' .Delete
' .Add Type:=xlExpression, Formula1:="=ISBLANK(RC)"
' .Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=NOW()"
' .Item(2).Interior.ColorIndex = 50
'End With
'
'Sheets("DW").Select
' Range("B1:N1").Select
' Selection.Interior.ColorIndex = 38
' Selection.Font.ColorIndex = 13
' Range("B2").Select

' _______________________________________________________________________

' Spare TAB INACTIVE. DELETE FROM FINISHED REPORT
' ================================================
'
' If a new collector is needed this tab can be reistated by removing the delete
' sheet code below and renaming the tab.


Sheets("Spare").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True

' ________________________________________________________________________




Sheets("Original Data").Select
Range("A4").Select



Application.ScreenUpdating = True

' ActiveWorkbook.SaveAs Filename:= _
' "C:\Documents and Settings\AWP00\Desktop\TB.xls", FileFormat:=xlNormal, _
' Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
' CreateBackup:=False

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\AWP00\Desktop\TB.xlsx", FileFormat:=xlOpenXMLWorkbook, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub
 
Upvote 0
I have taken a general look at your code and restructured vrious parts, if you keep the comments to a minimal level, don't have extra spaces, look to alter some of the statements to with and end with and there is one or to other things that could be considered, but for now look at this and see if you can run it. I would also look at the formula to place totals in, do each sheets place the formula in the same row?

The code could only be copied back I had used all the correct indents etc, but due to the amount it would convert to the html style

' MacroTB Macro
' Macro recorded 17/05/2007 by Wesfarmers Industrial & Safety
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False
ChDir "C:\Documents and Settings\AWP00\Desktop"
Workbooks.Open Filename:="C:\Documents and Settings\AWP00\Desktop\TB.csv"
Rows("1:10").Delete Shift:=xlUp
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With Columns("A:B")
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.ReadingOrder = xlContext
End With
Rows("1:1").Font.Bold = True
Columns("C:C").ColumnWidth = 25.43
Columns("A:A").ColumnWidth = 5.57
Columns("E:E").Delete Shift:=xlToLeft
Range("D1:N1").Value = Array("Terms", "Total", "Current", "Jan-30", "31-60 Days", "61-90 Days", "91-120 Days", "121-150 Days", "151+ Days", "Over 30", "Notes")
Columns("E:M").NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
Columns("N:N").Select
Selection.InsertIndent 1
Range("C1") = "Customer Name"
With Range("E1:M1")
.HorizontalAlignment = xlRight
.ReadingOrder = xlContext
End With
ActiveWindow.DisplayZeros = False
Range("F1") = "1-30 day"
Range("A2").Select
ActiveWindow.FreezePanes = True
Sheets("TB").Name = "Original Data"
' CREATE NEW SHEET NAMED EXPORT AND FORMAT
Sheets.Add
ActiveSheet.Name = "Export"
Sheets("Original Data").Select
Range("A1:N1").Select
With Selection.Interior
.ColorIndex = 15
End With
Range("A1:N1").Copy
Sheets("Export").Select
ActiveSheet.Paste
Sheets("Original Data").Select
ActiveSheet.Paste
Range("M2") = "=SUM(RC[-4]:RC[-1])"
' Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
With Range("E" & i)
If Not IsNumeric(.Value) Then .EntireRow.Delete
End With
Next i
' Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
With Range("A" & i)
If Not IsNumeric(.Value) Then .EntireRow.Delete
End With
Next i
' FILL DOWN FORMULA IN OVER 30 DAYS COLUMN
' New fill down formula
Range("M2").Formula = "=Sum(H2:L2)"
Range("M2").Copy Range("M2:M" & Cells(Rows.Count, 11).End(xlUp).Row - 1)
Range("A2").Select
Sheets("Export").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""Export Trial Balance"
.LeftFooter = "Printed &D"
.RightFooter = "&P"
.LeftMargin = Application.InchesToPoints(0.354330708661417)
.RightMargin = Application.InchesToPoints(0.354330708661417)
.TopMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.984251968503937)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintGridlines = True
.CenterHorizontally = True
.Orientation = xlLandscape
End With
Sheets("Original Data").Select
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""Trial Balance - Original Data"
.LeftFooter = "Printed &D"
.RightFooter = "&P"
.LeftMargin = Application.InchesToPoints(0.748031496062992)
.RightMargin = Application.InchesToPoints(0.748031496062992)
.TopMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.984251968503937)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintGridlines = True
.CenterHorizontally = True
.Orientation = xlLandscape
End With
Range("A3").Select
With Columns("D:D")
.ColumnWidth = 5.1
.HorizontalAlignment = xlCenter
.ReadingOrder = xlContext
End With
Columns("B:B").NumberFormat = "000000"
Columns("D:D").Insert Shift:=xlToRight
' PROPER FORMULA ON NAMES AND FILL DOWN
Range("D2") = "=PROPER(RC[-1])"
ActiveCell.Offset(0, 1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "x"
Selection.End(xlUp).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Range("C1").Select
Selection.Cut Destination:=Range("D1")
Columns("D:D").Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("C:C").Delete Shift:=xlToLeft
Columns("C:C").ColumnWidth = 21.29
Columns("D:D").ColumnWidth = 5.1
' PUT MONTHS MS COLUMN HEADINGS
Dim lRow As Long 'Row you want information to be on
' Dim lCol As Long
Dim lIncrement As Long
Dim lStart As Long 'Column you want to start in
Dim lEnd As Long 'Column you want to end with
lIncrement = -1 'Starting increment
lRow = 1
lStart = 6
lEnd = 12
' Formats range so the date will appear in the needed format mmm = Jan,
' mmm yy = Jan 07, mmm yyyy = Jan 2007
Range(Cells(lRow, lStart), Cells(lRow, lEnd)).NumberFormat = "mmm"
' This loop fills in the header values base on today's date. Each column is
' advanced based on the incrementation set in lIncrement
For lCol = lStart To lEnd
ActiveSheet.Cells(lRow, lCol).Value = DateAdd("m", lIncrement, Date)
lIncrement = lIncrement - 1
Next lCol
' FORMATTING ON EXPORT SHEET
Sheets("Export").Select
Columns("C:C").ColumnWidth = 21.29
Columns("E:E").ColumnWidth = 12.57
Columns("D:D").ColumnWidth = 5.14
Columns("F:H").ColumnWidth = 11.43
Columns("I:L").ColumnWidth = 10.57
Columns("M:M").ColumnWidth = 12.71
Columns("N:N").ColumnWidth = 40.57
With Range("D1:K1")
.HorizontalAlignment = xlRight
.ReadingOrder = xlContext
End With
Columns("D:D").Select
With Selection
.HorizontalAlignment = xlCenter
.ReadingOrder = xlContext
End With
Columns("A:A").EntireColumn.Hidden = True
Columns("E:M").NumberFormat = "#,##0.00"
Range("B2").Select
ActiveWindow.DisplayZeros = False
ActiveWindow.FreezePanes = True
' COPY HEADINGS FROM ORIGINAL DATA TO EXPORT
Sheets("Export").Select
Range("E1:K1").NumberFormat = "@"
Sheets("Original Data").Select
Range("D1:N1").Copy
Sheets("Export").Select
Range("D1").Select
ActiveSheet.Paste
Range("A2").Select
' ADD EXTRA SHEETS FOR EACH LEDGER
' These sheets are copied from Export tab so that formatting is correct.
Sheets("Export").Copy After:=Sheets(2)
Sheets("Export (2)").Copy After:=Sheets(3)
Sheets("Export (3)").Copy After:=Sheets(4)
Sheets("Export (4)").Copy After:=Sheets(5)
Sheets("Export (5)").Copy After:=Sheets(6)
Sheets("Export (6)").Copy After:=Sheets(7)
Sheets("Export (7)").Copy After:=Sheets(8)
Sheets("Export (8)").Copy After:=Sheets(9)
Sheets("Export (9)").Copy After:=Sheets(10)
Sheets("Export (10)").Copy After:=Sheets(11)
Sheets("Export (11)").Copy After:=Sheets(12)
Sheets("Export (2)").Name = "Spare"
Sheets("Export (3)").Name = "VL"
Sheets("Export (4)").Name = "DW"
Sheets("Export (5)").Name = "HH"
Sheets("Export (6)").Name = "HS"
Sheets("Export (7)").Name = "ML"
Sheets("Export (8)").Name = "MGMT"
Sheets("Export (9)").Name = "MS"
Sheets("Export (10)").Name = "RC"
Sheets("Export (11)").Name = "PG"
Sheets("Export (12)").Name = "CM"
' RENAME EACH SHEET
Sheets("Spare").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""Trial Balance Spare (8067 7201)"
End With
Sheets("PG").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""Trial Balance Pauline (8110 7202)"
End With
Sheets("DW").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""Trial Balance Danielle (8113 7203)"
End With
Sheets("HH").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHeader = "Trial Balance Heidi (8116 7204)"
End With
Sheets("HS").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""Trial Balance Harbans (8068 7065)"
End With
Sheets("ML").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""Trial Balance Marme (8112 7221)"
End With
Sheets("MGMT").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""Trial Balance Management Accounts"
End With
Sheets("MS").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""Trial Balance Mandeep (8201 7220)"
End With
Sheets("RC").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""Trial Balance Rosemary (8202 7222 Buying Group Members, 8204 7223 The Rest)"
End With
Sheets("VL").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""Trial Balance Vannessa (8203 7066)"
End With
Sheets("CM").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""Trial Balance Carissa (7067 8108)"
End With
' COPY & PASTE 7064 & 8064 TO EXPORT SHEET
Dim wsSht1 As Worksheet
Dim wssht2 As Worksheet
Dim lRowSht2 As Long
Dim llastrow As Long
Dim sCustCode As String
Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("Export")
llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2
For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "7064" Or sCustCode = "8064" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow
' TOTAL AT THE BOTTOM OF EXPORT SHEET IN BOLD
Sheets("Export").Select
Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B2").Select
' PAGE SET UP FORMATING
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""Trial Balance - Export"
.LeftFooter = "Printed &D"
.RightFooter = "&P"
.LeftMargin = Application.InchesToPoints(0.354330708661417)
.RightMargin = Application.InchesToPoints(0.354330708661417)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintGridlines = True
.CenterHorizontally = True
.Orientation = xlLandscape
End With
' COPY & PASTE 8067 & 7201 TO Spare SHEET
Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("Spare")
llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2
For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8067" Or sCustCode = "7201" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow
' PUT TOTALS AT THE BOTTOM OF Spare SHEET IN BOLD
Sheets("Spare").Select
Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
' COPY & PASTE 8110 & 7202 TO PG SHEET
Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("PG")
llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2
For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8110" Or sCustCode = "7202" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow

' PUT TOTALS AT THE BOTTOM OF A SHEET IN BOLD
Sheets("PG").Select
Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
' COPY & PASTE 8113 & 7203 TO DW SHEET
Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("DW")
llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2
For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8113" Or sCustCode = "7203" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow
' PUT TOTALS AT THE BOTTOM OF DW SHEET IN BOLD
Sheets("DW").Select
Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B2").Select
' COPY & PASTE 8116 & 7204 TO HH SHEET
Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("HH")
llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2
For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8116" Or sCustCode = "7204" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow

' PUT TOTALS AT THE BOTTOM OF HH SHEET IN BOLD
' ============================================
Sheets("HH").Select
Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
' COPY & PASTE 8068 & 7065 TO HS SHEET
Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("HS")
llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2
For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8068" Or sCustCode = "7065" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow
' PUT TOTALS AT THE BOTTOM OF HS SHEET IN BOLD
Sheets("HS").Select
Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
' COPY & PASTE 8112 & 7221 TO ML SHEET
Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("ML")
llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2
For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8112" Or sCustCode = "7221" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow
' PUT TOTALS AT THE BOTTOM OF ML SHEET IN BOLD
Sheets("ML").Select
Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
' COPY & PASTE 8900 & 7998 TO MGMT SHEET
Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("MGMT")
llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2
For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8900" Or sCustCode = "7998" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow
' PUT TOTALS AT THE BOTTOM OF MGMT SHEET IN BOLD
Sheets("MGMT").Select
Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("L1").Select
Range("L65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B2").Select
' COPY & PASTE 8201 & 7220 TO MS SHEET
Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("MS")
llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2
For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8201" Or sCustCode = "7220" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow
' PUT TOTALS AT THE BOTTOM OF MS SHEET IN BOLD
Sheets("MS").Select
Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
' COPY & PASTE 8202, 7222, 8204 & 7223 TO RC SHEET
Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("RC")
llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2
For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8204" Or sCustCode = "7223" Or sCustCode = "7222" Or sCustCode = "8202" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow
' PUT TOTALS AT THE BOTTOM OF RC SHEET IN BOLD
Sheets("RC").Select
Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
' COPY & PASTE 8203 & 7066 TO VL SHEET
Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("VL")
llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2
For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8203" Or sCustCode = "7061" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow
' PUT TOTALS AT THE BOTTOM OF VL SHEET IN BOLD
Sheets("VL").Select
Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
' COPY & PASTE 8108 & 7067 TO CM SHEET
Set wsSht1 = Sheets("Original Data")
Set wssht2 = Sheets("CM")
llastrow = wsSht1.Cells(wsSht1.Rows.Count, "a").End(xlUp).Row
lRowSht2 = 2
For lRow = 1 To llastrow
sCustCode = wsSht1.Cells(lRow, 1).Value
If sCustCode = "8108" Or sCustCode = "7067" Then
wsSht1.Rows(lRow).EntireRow.Copy Destination:=wssht2.Cells(lRowSht2, 1)
lRowSht2 = lRowSht2 + 1
End If
Next lRow
' PUT TOTALS AT THE BOTTOM OF CM SHEET IN BOLD
Sheets("CM").Select
Range("D1").Select
Range("D65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("E1").Select
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("F1").Select
Range("F65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("G1").Select
Range("G65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("H1").Select
Range("H65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("I1").Select
Range("I65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("J1").Select
Range("J65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Range("K1").Select
Range("K65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[" & -ActiveCell.Row + 2 & "]C:R[-1]C)"
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Font.Bold = True
Cells.Select
Range("B1").Activate
Selection.Sort Key1:=Range("L2"), Order1:=xlDescending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("B2").Select
' END OF COPYING AND PASTING ACCTS INTO COLLECTORS TABS
Sheets("Export").Select
Sheets("Export").Move Before:=Sheets(3)
Sheets("Original Data").Select
' COLOURED TABS
Sheets("CM").Tab.ColorIndex = 53
Sheets("VL").Tab.ColorIndex = 16
Sheets("RC").Tab.ColorIndex = 7
Sheets("MS").Tab.ColorIndex = 44
Sheets("MGMT").Tab.ColorIndex = 43
Sheets("ML").Tab.ColorIndex = 5
Sheets("HS").Tab.ColorIndex = 3
Sheets("HH").Tab.ColorIndex = 54
Sheets("DW").Tab.ColorIndex = 46
Sheets("PG").Tab.ColorIndex = 1
Sheets("Spare").Tab.ColorIndex = 6
Sheets("Export").Tab.ColorIndex = 14
Sheets("Original Data").Tab.ColorIndex = 9
' Rosemary
Sheets("RC").Select
Range("A1:N1").Interior.ColorIndex = 50
Rows("1:1").Font.ColorIndex = 13
' Unhide column A
Columns("A:A").ColumnWidth = 6.1
ActiveWindow.FreezePanes = False
Range("D2").Select
ActiveWindow.FreezePanes = True
' Harbans
Sheets("HS").Select
Range("B1:N1").Interior.ColorIndex = 3
Rows("1:1").Font.ColorIndex = 2
' Marme
Sheets("ML").Select
With Range("B1:N1")
.Font.ColorIndex = 5
.ColorIndex = 3
End With
ActiveWindow.FreezePanes = False
Range("D2").Select
ActiveWindow.FreezePanes = True
' Spare
Sheets("Spare").Select
With Range("B1:N1")
.Interior.ColorIndex = 5
.Font.ColorIndex = 6
End With
' Vannessa
Sheets("VL").Select
With Range("B1:N1")
.Interior.ColorIndex = 16
.Font.ColorIndex = 2
End With
' Heidi
Sheets("HH").Select
Range("B1:N1").Interior.ColorIndex = 6
ActiveWindow.FreezePanes = False
Range("D2").Select
ActiveWindow.FreezePanes = True
' Pauline
Sheets("PG").Select
With Range("B1:N1")
.Font.ColorIndex = 2
.Interior.ColorIndex = 1
End With
' Carrisa
Sheets("CM").Select
Range ("B1:N1")
.Font.ColorIndex = 5
.Interior.ColorIndex = 3
End With
' Danielle
Sheets("DW").Select
With Range("B1:N1")
.Font.ColorIndex = 38
.Interior.ColorIndex = 3
End With
' Spare TAB INACTIVE. DELETE FROM FINISHED REPORT
' If a new collector is needed this tab can be reistated by removing the delete
' sheet code below and renaming the tab.
Sheets("Spare").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Original Data").Select
Range("A4").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\AWP00\Desktop\TB.xlsx", FileFormat:=xlOpenXMLWorkbook, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
Click to expand...
 
Last edited:
Upvote 0
As far as I can see every use of Select/Selection/Activate can be eliminated from that code.

A very simple example is deleting a worksheet.

That can be done in one line.
Code: 
Sheets("Spare").Delete

Another quick one.
Code: 
With Sheets("DW").Range("B1:N1")
     .Font.ColorIndex =38
     .Interior.ColorIndex = 3
End With
Even going through and changing little things like that would cut down the code a bit.
 
Upvote 0
Thanks Norie for the tips. I have amended my code accordingly.

Are there any other things that you can see that can be streamlined? For example under the heading of "Put Totals at the Bottom of Sheet in Bold"? There is quite a lot of code involved in doing this.

Thank you.
 
Upvote 0
I asked you if your formula are always in the same row (not exact position, but that they need to go to the right).

You could also look at using an array on your sheets with your page setup and headers and footers then just add the extra parts for the individual, same with the sheet tabs and colouring and naming.

That would cut down your code a lot.
 
Upvote 0
Hi Trevor,

I am not sure what an array is or how to do this. Can you give me some guidance please?

Formulas are in the same column (M) and filled down. It adds columns H - L.

Appreciate your help.

Thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Formula to locate and remove outlying values from a large data set
Replies
3
Views
175
hagia_sofia
hagia_sofia
L
  • Solved
VBA Replace All '/' in a sheet, except for formulas
Replies
4
Views
159
MARK858
MARK858
Y
  • Question Question
VBA to copy, format, drag down data from other workbooks
Replies
1
Views
477
Yna26
Y
pcc
  • Solved
Why does a simple procedure take so long after conversion from .xls to .xlsm?
Replies
8
Views
201
pcc
pcc
O
  • Question Question
Frustrating intermittent VBA error dialog telling me that "User-define Type Not Defined"
Replies
1
Views
257
Micron
M

Forum statistics

Threads
1,224,506
Messages
6,179,158
Members
452,892
Latest member
yadavagiri

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top