It's not always best to refer to a worksheet by name.
You could use a worksheet variable that represents that new worksheet:
Dim NewWks as worksheet
Dim NextListWks as worksheet
set nextlistwks = worksheets("Next List")
set newwks = Worksheets.Add
with newks
.Name = Format(Date, "mmm dd yy")
nextlistwks.range("a1:A25").copy
.range("a1").pastespecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
end with
=====
I pasted into A1 of that new worksheet. Your code pasted into whatever was the
activecell in that worksheet's activewindow.
ckrogers wrote:
Hi. I have the following code (from a macro):
Worksheets.Add.Name = Format(Date, "mmm dd yy")
Sheets("Next List").Select
Range("A1:A25").Select
Range("A25").Activate
Selection.Copy
Sheets(Name).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets(Name).Select
I'm a beginner in VB and Macros ... how can I "store" the name of the new
worksheet I've created so I can select the right worksheet for the
PasteSpecial command?
Any and all help will be appreciated!
Cindy
--
Dave Peterson