I re-wrote a lot of your recorded macro code. the are things that
didn't make a lot of sense. It looks like you add column H then end up
delting this column.

To delte sheet 1 simply do this

Sheets("Sheet1").Delete



Isn't this much simplier to understand.

LastRow = Range("A" & Rows.count).End(xlup).row
Range("B2:B" & LastRow) = "CH"
Range("C262:C" & LastRow) = "0"
Range("D2:D" & LastRow) = "10"




With Sheets("sheet1")
LastRow = .Range("J", Rows.Count).End(xlUp).Row
.Range("L2:L" & LastRow).FormulaR1C1 = _
"=IF(RC[-2]<RC[-5], RC[-2]/RC[-5], 0)"

.Columns("L:L").NumberFormat = "0%"
'I commented out this line because it copies the same data back to
the same cell
'.Range("L2:L" & LastRow).Value = _
' .Range("L2:L" & Lastrow).Value

.Range("L1").Value = "% Below Min"

.Columns("L:L").EntireColumn.AutoFit
.Range("A1").Subtotal _
GroupBy:=1, _
Function:=xlAverage, _
TotalList:=Array(4, 5, 6, 7, 8, 9, 10, 11, 12), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True

.Outline.ShowLevels RowLevels:=2

Set visibleCells = Cells.SpecialCells(xlCellTypeVisible)
visibleCells.Copy _
Destination:=Sheets("Sheet2").Cells
End With

With Sheets("sheet2")

.Cells.Columns.AutoFit
LastRow = .Range("A" & Rows.Count).End(xlUp).Row

.Range("A1:L" & LastRow).Sort _
Key1:=.Range("D2"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

.Columns("H:H").Insert
.Range("H2") = "=RC[-1]*2"
.Range("H2").Copy _
Destination:=.Range("H2:H" & LastRow)

.Range("H2:H" & LastRow).Copy
.Range("H2").PasteSpecial _
Paste:=xlPasteValues
.Columns("H:H").NumberFormat = "0"

.Range("H1") = "Order Point Qty"

.Columns("B:G").Delete

'What are you doing! this will delte column H htat was added above
.Columns("C:G").Delete

.Columns("B:C").Insert

'this formula is is beijng over-written by the next line
'.Range("B2").FormulaR1C1 = "'=LEFT(A2,LEN(A2)-8)"
.Range("B2").FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-8)"


.Range("B2").Copy _
Destination:=.Range("H2:H" & LastRow)

.Range("B2:B" & LastRow).Copy
.Range("B2").PasteSpecial _
Paste:=xlPasteValues

.Range("A1").Cut
.Range("B1").Paste

.Columns("A:A").Delete

.Range("B1").FormulaR1C1 = "Location Code"
.Range("D1").FormulaR1C1 = "Number of Days"

.Columns("D:D").ColumnWidth = 15
.Range("B2:B" & LastRow) = "CH"

.Range("C262:C" & LastRow) = "0"
.Range("D2:D" & LastRow) = "10"
End With


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=164879

Microsoft Office Help