exporting multiple sheets to multiple files

[c_lansman]

I need to create a macro that will allow me to export two sheets at a time from my current workbook to a new workbook and password protect it.

ex.

current workbook

sheet1, ads,dr,hr, ...ect

export:
sheet1, ads then sheet1,dr then sheet1,hr ...ect

each to a new file with name corresponding to the second sheet exported.

ex.

sheet1, ads

filename: ads.xls

and have it password protected.

thanks

 

[c_lansman]

edit: made a fix

For i = 1 To UBound(sheetArray)
Sheets(sheetArray(i)).Select
Sheets("Sheet1").Select False
ActiveWindow.SelectedSheets.Copy
ActiveWorkbook.SaveAs Filename:= _
sheetArray(i), _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
Next i

 

[c_lansman]

have an add on question to the one above.

I now have it exporting just fine.


How would i go about exporting it to a shared http site?

ex///

ActiveWorkbook.SaveAs Filename:= _
"http://mylocation/" & Format(Now, "mm.dd.yy") & "/" & sheetArray(i), _
FileFormat:=xlNormal, Password:=passArray(i), WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

throws an error, what is wrong with the above code?

 

[c_lansman]

edit: sorry for all the posts, the site does not allow you to edit or delete your own posts.

Got the save to http working, now have an even stranger problem.

it starts saving and on the 6th file save it throws a runtime error 1004.

any reason why the first few work and the 6th fails?

I tried resaving each to my documents and they all saved just fine.

thanks

 

[jindon]

Doesn't 6th worksheet contain invalid character for the file name ?

 

[c_lansman]

its titled E&IS, is the & symbol invalid?

 

[jindon]

That looks fine.

What about the loop counter for the sheetArray ?
We can not see Lower/Upper bound of sheetArray.