Macros-creating new sheets

[Guest]

hi im tryin to create a macro to create new worksheets. This is basically
what is happening, i have 5 sheets mon-fri, n a weekly totals. i made a macro
which inserts 5 more extra sheets after the weekly totals, and pastes the
outlines on the new sheets. It worked =), however it doesnt work the second
time, it sort of overlaps and theres problems. Does anyone know how to solve
this?

Also i have came accross another problem which is naming the sheets. I
wanted to create a macro which renames a sheet by simply copying the data,
e.g. the date is M-14.02.06 , n the sheet name is M-14.02.06.
Again it worked, but wouldnt work for the other sheets, it says theres a
problem because not all sheets are named 'Sheet1'. Does anyone know how to
solve this?

Any help would be hugely appreciated, and thanks in advance =)

 

[Barb Reinhardt]

Please copy what you have so far in your macro. I'm cross-posting this to
the programming group for more assistance.

 

[Guest]

Hi thx for the fast reply,

this is the Macro which inserts 5 sheets, and set out the outlines on each
page (copy + paste, fonts etc).

Sub Add_New_DWS()
'
' Add_New_DWS Macro
' Macro recorded 16/02/2006 by Bonbon
'

'
Workbooks.Open Filename:= _
"C:\Documents and Settings\Bonbon\My
Documents\courseworks+Works\ICT\EXCEL ms.s\Project CW\Implementation\DM SALES
new design.xls"
Sheets("WPT-06.06.05--10.06.05").Select
Sheets.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Move After:=Sheets(8)
Sheets("Sheet1").Select
Sheets.Add
Sheets("Sheet2").Select
Sheets.Add
Sheets("Sheet3").Select
Sheets.Add
Sheets("Sheet4").Select
Sheets.Add
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("M-06.06.05").Select
Range("A1:H63").Select
Selection.Copy
Sheets("Sheet5").Select
Range("A1").Select
ActiveSheet.Paste
Columns("B:B").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D").EntireColumn.AutoFit
Columns("E:E").Select
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Range("E1:F1").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("D462").Select
Selection.ClearContents
Range("A1:H63").Select
Selection.Copy
Sheets("Sheet4").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Sheets("Sheet3").Select
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").Select
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Sheets("Sheet2").Select
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Sheets("Sheet1").Select
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Sheets("Sheet5").Select
Range("I51").Select
ActiveWindow.SmallScroll Down:=-39
Range("E1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("E1").Select
End Sub

It works the 1st time, but on the 2nd it says 'do you want to replace the
contents of the destination cells''. If thers any more detail u need pls
just state =)

The second problem was the naming sheets (by a date), so on each sheet there
is a cell where the user enters the date e.g. M-13.06.2005, or Tu-14.06.2005
etc. Then i want the macro to name the sheet as M-13.06.05, or Tu-14.06.05.
The macro i created was:

Sub NameSheetsDay()
'
' NameSheetsDay Macro
' Macro recorded 17/02/2006 by Brenda On
'

'
Range("E1").Select
Selection.Copy
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "M-13.06.05" [<----- they pointed out a problem
here]
Sheets("Sheet4").Select
Range("E1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Tu-14.06.05"
Sheets("Sheet3").Select
Range("E1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "W-15.06.05"
Sheets("Sheet2").Select
Range("E1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Th-16.06.05"
Sheets("Sheet1").Select
Range("E1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "F-17.06.05"
Sheets("M-13.06.05").Select
Range("D4").Select
End Sub

Thank you again, i really appreciate this =) If there is any other info u
want me to provide pls state.
look forward in hearing from you soon lol

Bonbon

 

[Guest]

Hi i was tempted to try again, and the naming sheets macro worked the 1st
time but didnt the next as it was selected the 'M-13.06.2005' instead of the
new week 'M-20.06.2005'.
Also the insterting sheets + setting out outline one works when i renamed
the worksheets to their date (M-13.06.2005 etc) however it misplaces them.
This is basically what is hapening:
There is 7 sheets which consists of : M-06.06.05 to F-10.06.05, then a
Weekly balance and a Weekly Pivot table.
Then when i hit the 'insert sheets macro', it inserts 5 more sheets after
the 1st 7 (which is right). Then i name the Date, and hit the macro to
rename, n it works.
But when i hit the 'insert sheets macro' again it places the new sheets
between the old week and the midweek, but it at the back. Or if its easier,
have the newest weeks in front?
I continued to see if the renaming works, but it says you cant rename sheets
the same name. I notice this is because the macro selects the 'M-13.06.2005'
everytime.. thats the error, but i dont know how to fix it =(

i hope i am not confusing you lol sorry.
pls help..

Bonbon