Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing Consecutive Number (One spreadsheet) on Each Sheet | Excel Programming | |||
Copying and Renaming New Sheet | Excel Programming | |||
Copying and renaming a file | Excel Discussion (Misc queries) | |||
copying and renaming sheets | Excel Programming | |||
Copying and renaming worksheets | Excel Programming |