On Apr 21, 3:40*pm, ajgillikin wrote:
Thanks. *that fixed that problem, and i thoguht I had the report
numbering figured out. *I am starting with report 1, then when I get
ready to make a new report, I need the new report to say 2 in cell
D9. *I tried RPT 2 D9=RPT1D9+1, which is fine for the first one, but
for RPT 3, I need it to go to D9 on report 2 and add 1. *i can't
figure out how to *make it select the sheet previous to active sheet.

code...
Sub CopyLastSheetAndName()
* * Dim ls As Long
* * ls = ActiveWorkbook.Sheets.Count
* * 'MsgBox ls
* * Sheets(Sheets.Count).Copy after:=Sheets(ls)
* * ActiveSheet.Name = "Rpt #" & ls + 1
* * Range("i4").Select
* * ActiveCell.FormulaR1C1 = Now()
* * Range("D10:G10").Select
* * ActiveWindow.ScrollColumn = 2
* * ActiveWindow.ScrollColumn = 3
* * ActiveWindow.ScrollColumn = 4
* * ActiveWindow.ScrollColumn = 5
* * ActiveWindow.ScrollColumn = 6
* * ActiveWindow.ScrollColumn = 7
* * ActiveWindow.ScrollColumn = 8
* * ActiveWindow.ScrollColumn = 9
* * ActiveWindow.ScrollColumn = 10
* * ActiveWindow.ScrollColumn = 11
* * ActiveWindow.ScrollColumn = 12
* * ActiveWindow.ScrollColumn = 13
* * ActiveWindow.ScrollColumn = 14
* * ActiveWindow.ScrollColumn = 15
* * ActiveWindow.ScrollColumn = 16
* * ActiveWindow.ScrollColumn = 17
* * ActiveWindow.ScrollColumn = 18
* * ActiveWindow.ScrollColumn = 19
* * ActiveWindow.ScrollColumn = 20
* * ActiveWindow.ScrollColumn = 21
* * ActiveWindow.ScrollColumn = 22
* * ActiveWindow.ScrollColumn = 23
* * Range("T15:T56").Select
* * Selection.Copy
* * ActiveWindow.SmallScroll Down:=-15
* * Range("S15").Select
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=False
* * ActiveWindow.ScrollColumn = 5
* * ActiveWindow.ScrollColumn = 4
* * ActiveWindow.ScrollColumn = 3
* * ActiveWindow.ScrollColumn = 2
* * ActiveWindow.ScrollColumn = 1
* * Range("A14:J21").Select
* * Application.CutCopyMode = False
* * Selection.ClearContents
* * ActiveWindow.SmallScroll Down:=42
End Sub
try

Sub CopyLastSheetAndName()
Dim ls As Long
ls = ActiveWorkbook.Sheets.Count
'MsgBox ls
Sheets(Sheets.Count).Copy after:=Sheets(ls)
ActiveSheet.Name = "Rpt #" & ls + 1
'========== SAS code above

Range("i4") = Now()
Range("T15:T56").Copy
Range("S15").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False
Range("A14:J21").ClearContents

'does this answer the question
With Sheets(ls).Range("d9")
.Value = Value + 1
End With
End Sub