Code that solved a previous post from Tom for me:
~~~~
Dim sh as Worksheet
for each sh in Thisworkbook.Worksheets
if sh.Name < "Sheet1" and sh.name _
< "Sheet2" and sh.Name < "Sheet3" then

' do something with Sh

end if
Next
~~~~
But as this code has a copy of each named sheet placed into a new workbook,
THEN copies a Utilisation Sheet(sheet with data & graphs from c/button) on
it.
I cannot sem to work out how to adapt the above to the code below.
The aim is that when other sheets are added to this workbook, then they ALSO
will be copied in this code when it is run.
The below code is designed to copy a designated range from each of the
sheets(employee timesheet) and place it (protected) in a new workbook for
that week,
then named the end of payweek date.
The below code also has a copy of the Utilisation sheet.
This has numerous commandbuttons on it to display graphs from the data
values on that sheet.
I need this info to be save also into the new workbook.



~~~~~~~~~~~~~~~~~~~~~
Sub Export_TimeSheets()
Call Unprotect
res = InputBox("Are you sure you want to Store the TimeSheets NOW ?" &
vbCrLf & vbCrLf & "This will not allow for anymore modifications...." &
vbCrLf & vbCrLf & vbTab & vbTab & "yes - (All lower case to continue).",
"....")
If res = "yes" Then
Application.ScreenUpdating = False
Application.DisplayAlerts = False
' Create a New WorkBook and Copy TimeSheets into it for Storage
' name 8 TimeSheet
Application.DisplayAlerts = False
With ActiveWorkbook.Sheets("name 8")
.Select
.Range("a1:u41").Copy
Workbooks.Add
Application.CommandBars("Task Pane").Visible = False
Range("A:A,D:D,G:G,J:J,M:M,P:P,S:S").Select
Selection.ColumnWidth = 1
Sheets("Sheet1").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet1").Range("A1:U42").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End With
' Place a Border Around TimeSheet
Range("A1:U42").Select
Range("U42").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
Range("A1").Select
' Name the TimeSheet the Value in Cell K2
ActiveSheet.Name = Worksheets("Sheet1").Range("K2").Value
Cells.Select
ActiveSheet.protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
Application.DisplayAlerts = False
' Temporarily Name & Save the WorkBook into the TimeSheets Folder....
ChDir "\\Office2\my documents\TimeSheets"
ActiveWorkbook.SaveAs Filename:= _
"\\Office2\my documents\TimeSheets\Temporary Name.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
' ADD name 7 TimeSheet Here <=====================================
Workbooks("TimeSheets").Activate
Application.DisplayAlerts = False
With ActiveWorkbook.Sheets("name 7")
.Select
.Range("a1:u41").Copy
Workbooks("Temporary Name").Activate
Sheets.Add
Sheets("Sheet3").Select
Application.CommandBars("Task Pane").Visible = False
Range("A:A,D:D,G:G,J:J,M:M,P:P,S:S").Select
Selection.ColumnWidth = 1
Sheets("Sheet3").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet3").Range("A1:U42").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End With
' Place a Border Around TimeSheet
Range("A1:U42").Select
Range("U42").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
Range("A1").Select
' Name the TimeSheet the Value in Cell K2
ActiveSheet.Name = Worksheets("Sheet3").Range("K2").Value
Cells.Select
ActiveSheet.protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
Application.DisplayAlerts = False
' ADD name 6 TimeSheet Here <=====================================
Workbooks("TimeSheets").Activate
Application.DisplayAlerts = False
With ActiveWorkbook.Sheets("name 6")
.Select
.Range("a1:u41").Copy
Workbooks("Temporary Name").Activate
Sheets.Add
Sheets("Sheet4").Select
Application.CommandBars("Task Pane").Visible = False
Range("A:A,D:D,G:G,J:J,M:M,P:P,S:S").Select
Selection.ColumnWidth = 1
Sheets("Sheet4").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet4").Range("A1:U42").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End With
' Place a Border Around TimeSheet
Range("A1:U42").Select
Range("U42").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
Range("A1").Select
' Name the TimeSheet the Value in Cell K2
ActiveSheet.Name = Worksheets("Sheet4").Range("K2").Value
Cells.Select
ActiveSheet.protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
Application.DisplayAlerts = False
' ADD name 5 TimeSheet Here <=====================================
Workbooks("TimeSheets").Activate
Application.DisplayAlerts = False
With ActiveWorkbook.Sheets("name 5")
.Select
.Range("a1:u41").Copy
Workbooks("Temporary Name").Activate
Sheets.Add
Sheets("Sheet5").Select
Application.CommandBars("Task Pane").Visible = False
Range("A:A,D:D,G:G,J:J,M:M,P:P,S:S").Select
Selection.ColumnWidth = 1
Sheets("Sheet5").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet5").Range("A1:U42").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End With
' Place a Border Around TimeSheet
Range("A1:U42").Select
Range("U42").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
Range("A1").Select
' Name the TimeSheet the Value in Cell K2
ActiveSheet.Name = Worksheets("Sheet5").Range("K2").Value
Cells.Select
ActiveSheet.protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
Application.DisplayAlerts = False
' ADD Name 5 TimeSheet Here <=====================================
Workbooks("TimeSheets").Activate
Application.DisplayAlerts = False
With ActiveWorkbook.Sheets("Name 5")
.Select
.Range("a1:u41").Copy
Workbooks("Temporary Name").Activate
Sheets.Add
Sheets("Sheet6").Select
Application.CommandBars("Task Pane").Visible = False
Range("A:A,D:D,G:G,J:J,M:M,P:P,S:S").Select
Selection.ColumnWidth = 1
Sheets("Sheet6").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet6").Range("A1:U42").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End With
' Place a Border Around TimeSheet
Range("A1:U42").Select
Range("U42").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
Range("A1").Select
' Name the TimeSheet the Value in Cell K2
ActiveSheet.Name = Worksheets("Sheet6").Range("K2").Value
Cells.Select
ActiveSheet.protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
Application.DisplayAlerts = False
' ADD name 4 TimeSheet Here <=====================================
Workbooks("TimeSheets").Activate
Application.DisplayAlerts = False
With ActiveWorkbook.Sheets("name 4")
.Select
.Range("a1:u41").Copy
Workbooks("Temporary Name").Activate
Sheets.Add
Sheets("Sheet7").Select
Application.CommandBars("Task Pane").Visible = False
Range("A:A,D:D,G:G,J:J,M:M,P:P,S:S").Select
Selection.ColumnWidth = 1
Sheets("Sheet7").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet7").Range("A1:U42").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End With
' Place a Border Around TimeSheet
Range("A1:U42").Select
Range("U42").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
Range("A1").Select
' Name the TimeSheet the Value in Cell K2
ActiveSheet.Name = Worksheets("Sheet7").Range("K2").Value
Cells.Select
ActiveSheet.protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
Application.DisplayAlerts = False
' ADD name 3 TimeSheet Here <=====================================
Workbooks("TimeSheets").Activate
Application.DisplayAlerts = False
With ActiveWorkbook.Sheets("name 3")
.Select
.Range("a1:u41").Copy
Workbooks("Temporary Name").Activate
Sheets.Add
Sheets("Sheet8").Select
Application.CommandBars("Task Pane").Visible = False
Range("A:A,D:D,G:G,J:J,M:M,P:P,S:S").Select
Selection.ColumnWidth = 1
Sheets("Sheet8").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet8").Range("A1:U42").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End With
' Place a Border Around TimeSheet
Range("A1:U42").Select
Range("U42").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
Range("A1").Select
' Name the TimeSheet the Value in Cell K2
ActiveSheet.Name = Worksheets("Sheet8").Range("K2").Value
Cells.Select
ActiveSheet.protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
Application.DisplayAlerts = False
' ADD name 2 TimeSheet Here <=====================================
Workbooks("TimeSheets").Activate
Application.DisplayAlerts = False
With ActiveWorkbook.Sheets("name 2")
.Select
.Range("a1:u41").Copy
Workbooks("Temporary Name").Activate
Sheets.Add
Sheets("Sheet9").Select
Application.CommandBars("Task Pane").Visible = False
Range("A:A,D:D,G:G,J:J,M:M,P:P,S:S").Select
Selection.ColumnWidth = 1
Sheets("Sheet9").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet9").Range("A1:U42").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End With
' Place a Border Around TimeSheet
Range("A1:U42").Select
Range("U42").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
Range("A1").Select
' Name the TimeSheet the Value in Cell K2
ActiveSheet.Name = Worksheets("Sheet9").Range("K2").Value
Cells.Select
ActiveSheet.protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
Application.DisplayAlerts = False
' ADD name1TimeSheet Here <=====================================
Workbooks("TimeSheets").Activate
Application.DisplayAlerts = False
With ActiveWorkbook.Sheets("name 1")
.Select
.Range("a1:u41").Copy
Workbooks("Temporary Name").Activate
Sheets.Add
Sheets("Sheet10").Select
Application.CommandBars("Task Pane").Visible = False
Range("A:A,D:D,G:G,J:J,M:M,P:P,S:S").Select
Selection.ColumnWidth = 1
Sheets("Sheet10").Range("A1:U42").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet10").Range("A1:U42").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End With
' Place a Border Around TimeSheet
Range("A1:U42").Select
Range("U42").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 56
End With
Range("A1").Select
' Name the TimeSheet the Value in Cell K2
ActiveSheet.Name = Worksheets("Sheet10").Range("K2").Value
Cells.Select
ActiveSheet.protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
Application.DisplayAlerts = False
' Copy/Paste Utilization Sheet with graphs
Call SheetMove
Columns("A:B").Select
Selection.ColumnWidth = 10
Columns("C:AA").Select
Selection.ColumnWidth = 5
Columns("X:X").Select
Selection.ColumnWidth = 7
ActiveSheet.Name = Worksheets("Utilization Sheet").Range("B1").Value
Range("A1").Select
Call SheetMove2
With Sheets("Sheet2").Delete
End With
With Sheets("Utilization Sheet")
.Select
ActiveSheet.Shapes("CommandButton22").Select
Selection.Delete
End With
Sheets("Leave Blank").Select
ActiveWindow.DisplayWorkbookTabs = True
' Name & Save the WorkBook into the TimeSheets Folder
ChDir "\\Office2\my documents\TimeSheets"
ActiveWorkbook.SaveAs Filename:= _
"\\Office2\my documents\TimeSheets\" & Sheet1.Range("E2").Text &
".xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=True, _
CreateBackup:=False
Call protect
ActiveWorkbook.Close
Sheets("Enter - Exit").Select
Dim myrange As Range
Set myrange = Range("E2")
Application.DisplayAlerts = True ' Turning it back on
MsgBox "The TimeSheets have been Stored as the Week Ending " &
myrange.Value & " .", , "...."
Workbooks("TimeSheets").Activate
Sheets("Enter - Exit").Select
Range("A1").Select
Call Unprotect
Application.ScreenUpdating = False
Sheets("name 8").Select
Call ClearTimeSheetValues
Sheets("Enter - Exit").Select
Range("A1").Select
Call protect
Application.DisplayAlerts = False
If Dir("\\Office2\my documents\TimeSheets\Temporary Name.xls") < ""
Then _
Kill "\\Office2\my documents\TimeSheets\Temporary name.xls"

Application.DisplayAlerts = True ' Turning it back on

Else
MsgBox "Please change what you need to and then try again.", , "...."
End If
End Sub

~~~~~~~~~~~~~~~~~~~~~