Help copying last sheet and renaming to next consecutive number

A

ajgillikin

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.
 
D

Donald Guillett

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.
Click to expand...

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
 
A

ajgillikin

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
 
D

Donald Guillett

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
Click to expand...
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
 
A

ajgillikin

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copying and Renaming New Sheet 4
Creating new sheet and renaming problem 2
Copy Data from several sheets into one issue 2
Copy a sheet and rename it 7
Sheet name changing dilemma 2
Adding a Worksheet and Renaming 3
Renaming a copied sheet programatically 3
Renaming sheet with VBA 2

Top