urgent

[someinput]

I have an excel template with password protected VBA.If i try to save, it will not allow unless i enter the password.I tried to write a VBA which will create a copy of current excel object and save it in a new location(by throwing save as dialoge control). But stil, since VBA is password protected, it is not possible to do so.So now i am trying to create a new XL application,workbook and copy the
current worksheets to new workbook.I am trying the following code.But it is throwing 'type mismatch'.


Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Set xlApp = CreateObject("Excel.Application&quot
xlApp.Workbooks.Add

Set xlbook = xlApp.ActiveWorkbook
Application.ActiveWorkbook.Sheets(0).Copy before:=xlApp.Workbooks(xlbook).Sheets(0)

 

[rmikesmith]

someinput,

Change this line:

Application.ActiveWorkbook.Sheets(0).Copy before:=xlApp.Workbooks(

xlbook

).Sheets(0)

to this:

Application.ActiveWorkbook.Sheets(0).Copy before:=xlApp.Workbooks(

xlbook.Name

).Sheets(0)

Workbooks() expects an index number or string containing the name of the workbook.

Regards,
Mike

 

[TopJack]

Try this as an alternative ......

****************************************************

Dim xlCurrBook As Excel.Workbook, xlCopyBook As Excel.Workbook

Set xlCurrBook = ActiveWorkbook 'declare current workbook
Set xlCopyBook = Workbooks.Add 'declare and add new workbook

xlCurrBook.Sheets(1).Copy before:=xlCopyBook.Sheets(1) 'copy sheets from current to new

****************************************************

If you want to copy multiple sheets maybe you could use :-

xlCurrBook.Sheets(Array(1, 2, 3)).Copy before:=xlCopyBook.Sheets(1)

Hope this helps.