VBA : VBA Assistance

rpaulson · Apr 23rd 2004

You guys are awsome. May this site live forever.

Here is my code

Set Show = ActiveSheet

Cells.Select
Selection.Copy

Sheets.Add
Sheets("sheet4").Select

Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("sheet4").Select
Sheets("sheet4").Name = "410w1"

Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("S14").Select

Sheets(Show).Select
Range("AA19").Select
End Sub

My Problems.
I Need to change the added name because each time it runs it adds another sheet, SHeet4..5..6....

What I really want is a prompt for the user to enter a week number 1 2 3 ... then the new sheet name to be (show) which is the active shhet name with the number attatched.

Example:
If the macros is run from a sheet name bill, and the user input 15. the new sheet name would be bill15.

Thnaks for the help.

Ross

yjoshi · Apr 23rd 2004

Following code should solve your problems.

Not really sure why you used paste special... but you can still replace the paste command with your old command, if you need, and it also works fine.

Code

Sub Copy_to_New_Sheet()
Show = ActiveSheet.Name
Selection.Copy
Sheets.Add
ActiveSheet.Paste
num = InputBox("Enter a valid number")
ActiveSheet.Name = Show + num
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets(Show).Select
Range("AA19").Select
End Sub

Display More

VBA : VBA Assistance

Participate now!

Share