copying worksheet through macro

[Guest]

I have recorded a macro which copies a worksheet and its contents on to a new
worksheet. the sheets are named (19801, 19802, 19803, 19804 etc etc). When i
run the macro its fine for first one but when i run it again all it does is
copies the first workbook not the last one. So i need it to copy the last
numbered worksheet. Also as a part of the macro i want it to copy and paste
special, values only so it does not keep updateing itself with the formulas
so i have a history of sheets. Find below the code used.
Sub newsheet()
'
' newsheet Macro
' Macro recorded 08/10/2004 by Jason Watson
'

'
Sheets("19817").Select
Sheets("19817").Copy After:=Sheets(21)
Sheets("19817 (2)").Select
Sheets("19817 (2)").Name = "NEW, rename to next number"
Sheets("19817").Select
ActiveWindow.SmallScroll Down:=33
Range("A1:H67").Select
Range("H67").Activate
ActiveSheet.Shapes("Picture 1").Select
Range("D1").Select
ActiveWindow.SmallScroll Down:=36
Range("A1:H67").Select
Range("H67").Activate
Selection.Copy
ActiveWindow.SmallScroll Down:=-60
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("NEW, rename to next number").Select
ActiveWindow.SmallScroll Down:=-21
End Sub


HELP!!!!!!!

Justin

 

[Myrna Larson]

It copies the same sheet because the sheet name is hard-coded in the macro.
Deciding which sheet to copy, based on it's name, isn't something you can
accomplish with the macro recorder.

If I understand you correctly, the worksheet names are numeric. You want to
copy the highest numbered sheet to the end of the workbook, and name it with
the next number in sequence. You can get the code for the "pieces" of this
operation from the macro recorder, but not everything. Here's code that does
what I just described.

But I'm not sure it's correct. Let's say the last sheet is 18001. You make a
copy, name it 18002, and convert the formulas to values. When you run the
macro again, it will now copy sheet 18002. Presumably there won't be any
changes here, since when you copied it the first time, you removed the
formulas. So why copy it again? OTOH, maybe you didn't replace all the
formulas, just some of them.

If you are trying to construct an "audit trail", don't you have a "master"
sheet where you make changes? If so, maybe what you want is to always copy
that *master sheet*, giving the copy the next number in sequence and
converting the formulas to values.

Anyway, heres the code to copy the sheet with the highest number, as you
described.

Option Explicit

Sub CopyLastSheet()
Dim LastN As Long
Dim N As Long
Dim S As Long
Dim SheetCount As Long

'1st task is to find the last sheet, the one
'with the highest name/number
SheetCount = ThisWorkbook.Worksheets.Count
LastN = 0
For S = 1 To SheetCount
N = CLng(Worksheets(S).Name)
If N > LastN Then LastN = N
Next S

'the next line copies the sheet with the highest number
'if the macro has been run at least once before, that sheet is itself
'a copy and has no formulas
'are you sure this is the sheet you want to copy?
Worksheets(CStr(LastN)).Copy After:=Worksheets(SheetCount)

'are you sure you don't want something like this instead of the above???
'Worksheets("Master").Copy After:=Worksheets(SheetCount)

SheetCount = SheetCount + 1
With Worksheets(SheetCount)
LastN = LastN + 1
.Name = Format$(LastN)
With .Range("A1:H67")
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
End With
Application.CutCopyMode = False
End Sub