Macro Wont Run On Excel 2007

W

wayneshirley

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

I have a macro that was developed using Excel 2003 (TB.xls). Recently I have upgraded to Excel 2007. Each time I run the macro an error message appears:

Significant loss of functionality
This workbook contains data in cells outside the row & column limit of the selected file format.

Another person in my office uses the same macro, originally with Excel 2003 but now with Excel 2007 and it works perfectly on her PC.

Any ideas please?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Looks like the macro is doing a save-as to the 97-2003 file format and the workbook being saved has acquired data (or other features) beyond the limits Excel 97-2003 can handle (columns/rows). Without your code I can't tell you more though.
 
Upvote 0
Post the code here, for a start. That's the whole point of a forum :-)
 
Upvote 0
Hi Glenn,

Here it is and thank you.

CODE:
Sub MacroTB30daysMidMth()
'
' 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

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("D:D").Delete Shift:=xlToLeft



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


Columns("D:L").NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "

Columns("M:M").Select
Selection.InsertIndent 1
Range("C1").Select
ActiveCell.FormulaR1C1 = "Customer Name"



' Right align D1 to L1
' --------------------

With Range("D1:L1")
' 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"

Sheets.Add
ActiveSheet.Name = "Export"

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



Range("A1:M1").Copy

Sheets("Export").Select
ActiveSheet.Paste
Range("A2").Select
Sheets("Original Data").Select

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

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

With Range("A1", Range("A" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:="<6999", Operator:=xlOr, _
Criteria2:=">=9000"
.Offset(1).EntireRow.Delete
.AutoFilter Field:=1, Criteria1:="=?*"
.Offset(1).EntireRow.Delete
.AutoFilter
End With

' 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


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


' New fill down formula
' ---------------------
Range("L2").Formula = "=Sum(G2:K2)"
Range("L2").Copy Range("L2:L" & 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

' END OF GENERAL FORMATTING
' =========================

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



' 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



' 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 = 5
' lEnd = 11
'
'' 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


With Range("E1:K1")
.Formula = "=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-COLUMN(A1)+1,1),""mmmm"")"
.Value = .Value
End With




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


Sheets("Export").Select
Range("A2").Select
Columns("C:C").ColumnWidth = 21.29
Columns("D:E").ColumnWidth = 12.57

' Columns("D:D").ColumnWidth = 12.57
' Columns("E:E").ColumnWidth = 12.57

Columns("F:H").ColumnWidth = 11.43

' Columns("F:F").ColumnWidth = 11.43
' Columns("G:G").ColumnWidth = 11.43
' Columns("H:H").ColumnWidth = 11.43

Columns("I:L").ColumnWidth = 10.57

' Columns("J:J").ColumnWidth = 10.57
' Columns("K:K").ColumnWidth = 10.57
' Columns("L:L").ColumnWidth = 10.57

Columns("M:M").ColumnWidth = 40.57



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

Columns("D:L").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 = "@"

Range("B2").Select
Sheets("Original Data").Select
' Range("A2").Select


Sheets("Original Data").Select
Range("E1:K1").Select
Selection.Copy
Sheets("Export").Select
Range("E1").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 = "PG"
Sheets("Export (4)").Name = "DW"
Sheets("Export (5)").Name = "HH"
Sheets("Export (6)").Name = "KS"
Sheets("Export (7)").Name = "ML"
Sheets("Export (8)").Name = "MGMT"
Sheets("Export (9)").Name = "MS"
Sheets("Export (10)").Name = "RC"
Sheets("Export (11)").Name = "VL"
' Sheets("Export (12)").Name = "PC"


' 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("KS").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
' .PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
' .LeftHeader = ""
.CenterHeader = "&""Arial,Bold""Trial Balance Kelly (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("PC").Select
' With ActiveSheet.PageSetup
' .PrintTitleRows = "$1:$1"
'' .PrintTitleColumns = ""
' End With
' ActiveSheet.PageSetup.PrintArea = ""
' With ActiveSheet.PageSetup
'' .LeftHeader = ""
' .CenterHeader = "&""Arial,Bold""Trial Balance Pankaj"
'' .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.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




' 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



' Copy accounts starting with A and numerals from Spare to VL
' --------------------------------------------------------

' Dim lastrow As Long, i As Integer, result As String, nextrow As Long
' lastrow = Sheets("Spare").Range("C" & Rows.Count).End(xlUp).Row
' nextrow = Sheets("VL").Range("A" & Rows.Count).End(xlUp).Row + 1
'
' Sheets("Spare").Activate
' For i = 1 To lastrow
' result = Left(Range("C" & i), 1)
' Select Case result
' Case "A", "1", "2", "3", "4", "5", "6", "7", "8", "9", "0"
' Range("A" & i).EntireRow.Copy Sheets("VL").Range("A" & nextrow)
' Rows(i).EntireRow.Delete (xlShiftUp)
' i = i - 1
' nextrow = nextrow + 1
' Case Else
' End Select
' Next i







' 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 KS 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("KS")

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 KS SHEET IN BOLD
' ============================================

Sheets("KS").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

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 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 = "8202" Or sCustCode = "7222" Or sCustCode = "7223" Or sCustCode = "8204" 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
' 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 = "7066" 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

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



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

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

' ActiveWorkbook.Sheets("PC").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("KS").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:M1").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

' Kelly
' -----

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

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


' Marme
' -------

Sheets("ML").Select
Range("B1:M1").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:M1").Select
Selection.Interior.ColorIndex = 5
Selection.Font.ColorIndex = 6
Range("B2").Select

' Vannessa
' --------

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

' Heidi
' -----

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

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


' Pauline
' -------

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

' Danielle
' -----

' 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

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 NOW 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("A2").Select





Application.ScreenUpdating = True
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\AWP00\Desktop\TB.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub
 
Upvote 0
If you put a breakpoint in before the SaveAs, you can look at your worksheets and see if any have data past row 65536 ... worth a look for an initial investigation.
 
Upvote 0
Hi there,

My macro opens a csv file (TB.csv), formats then copies the data onto seperate worksheets. Roughly speaking by customer name A-D on sheet 1, E-K on sheet 2 etc. Thereafter its saves as TB.xls.

I have done more testing and it is creating the formated worksheets ok but is deleting the data instead of coping it. Even the worksheet with the original complete set of data is gone.

Does this give anyone a clue as to what the problem is?

Thank you.
 
Upvote 0
Change these lines:
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\AWP00\Desktop\TB.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

To:

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\AWP00\Desktop\TB.xlsx", FileFormat:=xlOpenXMLworkbook, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
 
Upvote 0
Hi Jan,

I tried the new Save As code but there is no change. Formatting OK but no data>

Can you help please?

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

Similar threads

T
  • Question Question
Does anybody know of a service that runs Excel on a server so I can run macros
Replies
1
Views
231
Drrellik
Drrellik
S
  • Solved
run macro from network xlam addin via vba
Replies
2
Views
330
solidENM
S
M
  • Question Question
Memory resource issue when opening and closing excel files
Replies
0
Views
288
Mike UK
M
P
  • Question Question
Excel Macro from 32 to 64 will not run
2
Replies
13
Views
550
Pophil
P
S
  • Solved
Macro runs different when stepping through code
Replies
3
Views
458
bobsan42
bobsan42

Forum statistics

Threads
1,224,521
Messages
6,179,291
Members
452,902
Latest member
Knuddeluff

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