Create WB, create new Sht, paste data. (Macro not working)

[Rick S.]

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

 

[Bob Phillips]

I cannot see the code that creates the new workbook, but what does failing
mean, it errors, it doesn't do what you expect, or what?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Susan]

no XP pro here, but have found copy/paste can be a nightmare filled
with bugs. why not just save that sheet as a new workbook? i know
i've seen that done........
just an idea.
susan


or instead of copy/paste,

 

[Rick S.]

Good morning Bob!
I use the word fails to mean it is not completing the task, no errors are
being reported.
The new workbook is created here:
'=========
ActiveWorkbook.SaveAs Filename:= _
sNewItem & "\Template ISO " & sSheetName & " Audit
mm.dd.yy.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False,
WriteResPassword:="2000"
'=========
This string of text and variables (from above) sets the new work book name
and folder location.
'=========
sNewItem & "\Template ISO " & sSheetName & " Audit mm.dd.yy.xlsx",
'=========
If it's in Quotes "" its text, else it is a variable. The variable sNewItem
is the Folder location string, IE, "C:\MyFolder".

--
Regards

Rick
XP Pro
Office 2007

 

[Susan]

http://groups.google.com/group/micr...save+each+sheet+new+workbook#26456db97a94117c

not excel 07 but idea.
susan

 

[Rick S.]

Initially I do save a worksheet as a new workbook. But the second workbook I
open and the sheet I select to copy will not allow me to copy the entire
sheet to a new workbook, this is an internal Excel 2007 error, hence I am
using Copy/Paste data instead.
Excel cannot insert the sheets into the destination workbook, because it
contains fewer rows and columns than the source workbook. To move or copy the
data to the destination workbook, you can select the data, and then use Copy
and Paste commands to insert it into the sheets of another workbook.I had this posted on another forum.
http://www.excelforum.com/showthread.php?p=1845240#post1845240
And the answer is.....Author: shg (Moderator at ExcelTip.com)
Attempting to copy a worksheet from an older version of Excel into a 2007
workbook? Excel 2007, in contrast to older versions, has 1M rows and 64K(?)
columns.

So select all the cells in the old worksheet (Ctrl+A, or click in the upper
left corner of the row and column headers), copy, and paste into the 2007
worksheet.I am forced to copy/paste. :shrugs shoulders:

--
Regards

Rick
XP Pro
Office 2007

 

[Susan]

rats for you. sorry, wasn't aware of the issue. i hope bob can help
you work it out.
susan