Help copying last sheet and renaming to next consecutive number
 

I have a daily report I am putting together, and need help with the
VBA coding for a macro. I have RPT 1 as the first sheet, and would
like to copy it, paste it at the end, rename it RPT 2, then move some
data, rename a few cells, clear some data. I have the last part
pretty much figured out. My problem is setting the macro to copy the
last sheet in the book, then rename it to RPT n (or previous report
+1). I have some cobbled together code, but would rather not post it,
and I've searched through the posts, but nothing fits what I am
looking for. BTW, this is my first run with macros and programming.
Thanks for the help.

Help copying last sheet and renaming to next consecutive number
 

On Apr 21, 1:35*pm, ajgillikin wrote:
I have a daily report I am putting together, and need help with the
VBA coding for a macro. *I have RPT 1 as the first sheet, and would
like to copy it, paste it at the end, rename it RPT 2, then move some
data, rename a few cells, clear some data. *I have the last part
pretty much figured out. *My problem is setting the macro to copy the
last sheet in the book, then rename it to RPT n (or previous report
+1). *I have some cobbled together code, but would rather not post it,
and I've searched through the posts, but nothing fits what I am
looking for. *BTW, this is my first run with macros and programming.
Thanks for the help.

Perhaps this will help. ALLWAYS post your code for comments

Option Explicit
Sub CopyLastSheetAndName()
Dim ls As Long
ls = ActiveWorkbook.Sheets.Count
'MsgBox ls
Sheets(Sheets.Count).Copy after:=Sheets(ls)
ActiveSheet.Name = "Rpt " & ls + 1
End Sub

Help copying last sheet and renaming to next consecutive number
 

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

Help copying last sheet and renaming to next consecutive number
 

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

Help copying last sheet and renaming to next consecutive number
 

I tried the With Sheets part, but maybe I have it in the wrong
place...

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()
With Sheets(ls).Range("d9")
.Value = Value + 1
End With
Range("D10:G10").Select
ActiveWindow.ScrollColumn = 2

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

Range("A14:J21").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveWindow.SmallScroll Down:=42
Range("P75:P79").Select
Selection.Copy
Range("L75:L79").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("M75:O79").Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub