The code below should create a new workbook, add a sheet, Open an existing
WB, copy data then past the data onto the new sheet in the new WB. But
somewhere it is failing and I do not see why.
When I read the macro I see it as top down activity, but I am obviously
missing something somewhere.

Creates the new workbook (Ok, sNewItem & some text)
Opens a WB to copy data from (Ok, "Template IAR Sheet1.xlsx")
Selects the data (Ok, Cells.Select)
copies Selection (Ok, Selection.Copy)
Create new Sht (Fails)
Paste data on new Sht (Fails, because new Sht fails)
ActiveWorkbook.Close (Fails to close "Template IAR Sheet1.xlsx")
Note: passwords in macro are nothing, they are generic values for testing

'============
Sub CreateNewWorkbooks()
Application.ScreenUpdating = False

sNewItem = InputBox(prompt:="Enter Entire Path for Folder/File Location!")
On Error GoTo EndIt
Set wb = ActiveWorkbook
Dim wkSheet As Worksheet
For i = 1 To Worksheets.Count
Sheets(i).Activate
With ActiveSheet
sSheetName = ActiveSheet.Name
If ActiveSheet.Name = "Sheet1" Then
GoTo EndIt
End If
Sheets(sSheetName).Select
Sheets(sSheetName).Copy
ActiveWorkbook.SaveAs Filename:= _
sNewItem & "\Template ISO " & sSheetName & " Audit
mm.dd.yy.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False,
WriteResPassword:="2000"
'open "Template IAR Sheet1.xlsx" and create copy of sheet1
Workbooks.Open Filename:= _
"M:\Qadocs\ISO DOCS\ISO Audits\Templates\Template IAR
Sheet1.xlsx", _
WriteResPassword:="2000"
Windows("Template IAR Sheet1.xlsx").Activate
Cells.Select
Selection.Copy
'Select new workbook, create new sheet and paste new cells
Windows(sNewItem & "\Template ISO " & sSheetName & " Audit
mm.dd.yy.xlsx").Activate
Sheets.Add After:=Sheets(Sheets.Count)
Cells.Select
ActiveSheet.Paste
Workbooks("M:\Qadocs\ISO DOCS\ISO Audits\Templates\Template IAR
Sheet1.xlsx").Activate
ActiveWorkbook.Close
Windows(wb.Name).Activate
End With
Next i
EndIt:
Application.ScreenUpdating = True
End Sub
'============

--
Regards

Rick
XP Pro
Office 2007