Saving Worksheet "Name" in a Macro
 

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

Saving Worksheet "Name" in a Macro
 

hi
use variables.
Dim nam As String
Worksheets.Add.Name = Format(Date, "mmm dd yy")
nam = ActiveSheet.Name
Sheets("somesheet").Select
Sheets(nam).Select

variables are not stored permanently. when the sub finishes, all variable
are cleared from memory.

regards
FSt1


"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

Saving Worksheet "Name" in a Macro
 

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

Saving Worksheet "Name" in a Macro
 

Cindy

When you add a worksheet it becomes the activesheet.

You don't need all those "Selects". Just use the values from "Next List" and
place them into the new sheet.

Sub add_n_copy()
Dim ws As Worksheet
Worksheets.Add.Name = Format(Date, "mmm dd yy")
ActiveSheet.Range("A1:A25").Value = _
Sheets("Next List").Range("A1:A25").Value
End Sub


Gord Dibben MS Excel MVP

On Fri, 28 Dec 2007 12:58:01 -0800, 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

Saving Worksheet "Name" in a Macro
 

Perfect ... thanks!

"FSt1" wrote:

hi
use variables.
Dim nam As String
Worksheets.Add.Name = Format(Date, "mmm dd yy")
nam = ActiveSheet.Name
Sheets("somesheet").Select
Sheets(nam).Select

variables are not stored permanently. when the sub finishes, all variable
are cleared from memory.

regards
FSt1


"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