Macro Help

[JCG82]

Good morning - afternoon all,

I have a shared worksheet my self and a few others key pricing into weekly, more or less a price check for stores we service. I have created another workbook that has the same basic template with links to the "shared" worksheet. In this workbook Im trying to run a macro to copy the pricing "template" into a new tab in the workbook. I keep getting this error as it pertains to "sheet". I would like the tab that is created to be named whatever the current day is if possible.

Thanks for any pointers or advice!

Sub StoreWeeklyData()
'
' StoreWeeklyData Macro
' Macro recorded 3/16/2009 by : Creates a tab for current week pricing data.
'
' Keyboard Shortcut: Ctrl+s
'
Sheets("Template-Link").Select
Range("A1:AM61").Select
Selection.Copy
Sheets("Template-Link").Select
Sheets.Add
Sheets("sheet").Select <-- this is my error.. not sure how to fix
Sheets("Sheet2").Name = "WK 1"
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Application.CutCopyMode = False
ActiveWindow.DisplayZeros = False
ActiveWindow.SmallScroll Down:=-12
Range("B6").Select
End Sub

 

[xld]

Try this

Sub StoreWeeklyData()
'
' StoreWeeklyData Macro
' Macro recorded 3/16/2009 by : Creates a tab for current week pricing data.
'
' Keyboard Shortcut: Ctrl+s
'
Sheets("Template-Link").Range("A1:AM61").Copy
Sheets.Add.Name = "WK 1"
With Sheets("WK 1")
    .Range("A1").Paste
    .Columns("A:A").EntireColumn.AutoFit
End With
End Sub

 

[Andrew Poulsom]

When you add a worksheet it becomes the ActiveSheet, so there is no need to select it. You can add a sheet and name it like this:

Worksheets.Add.Name = "MyNewSheet"

 

[yytsunamiyy]

Quick and dirty:

Put the template in the same workbook as your tracking sheet.

Try the following code:

Sub StoreWeeklyData_1()

    Sheets("Template-Link").Copy Before:=Sheets(1)
    Sheets(1).Name = Date

End Sub

This will copy your entire template sheet to a new tab which will be the new first tab in your workbook and rename that tab with today's date.

If you want it to be inserted at the end use:

Sub StoreWeeklyData_2()

    Sheets("Template-Link").Copy After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = Date
    
End Sub

 

[JCG82]

Thanks for all the prompt response's !

The only issue with storing the pricing "data" tab's in the file we use to update the pricing is that it has allot of link's to other things and I'm trying to keep this page from being "laggy" if you will.

I'm going to try the suggestions and see if I can make it work.. thanks for all the help!

 

[yytsunamiyy]

According to a PM the OP sent me, (s)he encounters the following error message when running my 2nd code:

While renaming a sheet or chart, you entered an invalid name. Try one of the following:

*Make sure the name you entered does not exceed 31 characters"

For me the posted code works fine under XL07 German Version on a German-language Win XP SP3 system (Date format = dd.mm.yyyy). Does anyone know of any restrictions in other (language) XL versions concerning the use of a date as a worksheetname?

 

[JCG82]

It names the file "template-Link (2)" for some reason. I tried using Excel 07 and 03

Sub StoreWeeklyData_2()
Sheets("Template-Link").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Date <-- stats error is here..

End Sub

 

[yytsunamiyy]

It names the file "template-Link (2)" for some reason. I tried using Excel 07 and 03

Sub StoreWeeklyData_2()
Sheets("Template-Link").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Date <-- stats error is here..

End Sub

This is normal. As I said earlier - the code makes a COPY of your template sheet first and renames it to the date the code is run on in the second step. when copying a sheet in excel it is automatically named "whatever (2)". You can see this for yourself if you manually right-click on the tab, select copy or move, tick create a copy and click on ok. As the error happens when we try to rename the sheet, that line is not executed and the sheet keeps the old "new" name "whatever (2)".

 

[JCG82]

Got cha.. but should the error appear ? I thought it would name the tab the current "date" not, am I off track ?

 

[Andrew Poulsom]

For me the posted code works fine under XL07 German Version on a German-language Win XP SP3 system (Date format = dd.mm.yyyy). Does anyone know of any restrictions in other (language) XL versions concerning the use of a date as a worksheetname?

A worksheet name can contain a dot but not a forward or backward slash. So it can't be eg 16/03/2009 which is the format in the UK. It's safe to use:

Sheets(1).Name = Format(Date,"ddmmyyyy")