Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Why won't the "record macro" button show on worksheet | Excel Discussion (Misc queries) | |||
Macro to Create New Worksheet and Reference Cell in Old Worksheet As Tab Name - "Object Required" Error | Excel Discussion (Misc queries) | |||
Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |