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("D4
62").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