Solved: creating files from dynamic sheet names

[kishlaya]

hello everybody
i am quite new to vba. i am facing some problems in automating a project.i'll describe it briefly (please contact in case of any clarifications.)
i have a number of sheets in my workbook. some of them are used to input data. these input sheets' range will decide the new names of the already created output sheets.
now my problem is that i want all the output sheets to be stored as separate excel files with the same name as that of the sheets using a single button. may i remind u again that the names of these output sheets is dynamic as they depend upon the input data.


The file name for each output should be same as the respective worksheet name as displayed on the sheet name tab.
This sheet name will change based on the input files every time).
After the individual files are created, there should not be any buttons or macros in those files.
The main Template file should not be affected (Formulas & VB codes should still function in the main file).
and one more limitation is dat these sheets are all protected.:help:banghead:

Sub CommandButton9_Click()
ThisWorkbook.Sheets("SMP").Visible = True
ThisWorkbook.Sheets("Main Menu").Visible = False
ThisWorkbook.Sheets("SEM").Visible = False
ThisWorkbook.Sheets("BOM").Visible = False

ThisWorkbook.Sheets("SMP").Unprotect Password:=""
ThisWorkbook.Sheets("SMP").Rows("14:58").Select
Selection.Rows.AutoFit
ThisWorkbook.Sheets("SMP").Range("A1").Select
ThisWorkbook.Sheets("SMP").Protect Password:=""
ActiveSheet.Name = "SMP" & ThisWorkbook.Sheets("SMP").Range("D10").Value & "-01"
End Sub

[mdmackillop]

Welcome to VBAX

I'm not totally clear, but something like this?
Sub ExportSheet()
Dim sh As String
sh = InputBox("Enter sheet name")
Sheets(sh).Copy
ActiveWorkbook.SaveAs "C:\AAA\" & sh & ".xls"
ActiveWorkbook.Close
End Sub

[Bob Phillips]

Something like



For Each sh In ActiveWorkbook.Worksheets

sh.Copy
ActiveWorkbook.SaveAs sh.Name & ".xls"
ACtiveWorkbook.Close
Next sh

[kishlaya]

actually the newly created output sheets should be saved as a different excel files with same names on a single click. i am afraid that we cant use input boxes and save as pop ups. is there a way i can send u the file personally??

[kishlaya]

dear xld
the sheets are protected so we cant select them as we select the normal unprotected sheets.

[mdmackillop]

Remove any private data and post it using Manage Attachments in the Go Advanced reply section.

Does XLD's solution suffice?

[kishlaya]

i tried XLD's solution, but as i said the since the sheets are protected and hidden so m not able to run that code

[kishlaya]

Dear XLD
thanks very much for ur help man. to some extent your code is working but i want a single button for all the sheets. i inserted your code in the button which creates one of the output files. but i have different buttons for each of those output sheets creation. am i making sense here??? is dat possible???

[mdmackillop]

I still don't follow what you are after. Can you give a specific example?

[Bob Phillips]

Just have the button assigned to the same macro and use



Activesheet.Copy
ActiveWorkbook.SaveAs Activesheet.Name & ".xls"
ActiveWorkbook.Close

[kishlaya]

dear XLD & MDMACKILLOP (http://www.vbaexpress.com/forum/member.php?u=87)
if u see the file which i have attached there appears one sheet after opening it called the main menu. in that sheet u can see there are three input sheets and eight output sheets.
first we enter the data in the 3 three input sheets. based on this data when we click the output sheets button the corresponding sheets will appear. the name of these newly created sheets will differ each time we give input data.
hope everything is clear till now.
now my problem is i want to create a button in the main menu sheet which will generate all the eight sheets in one clickand save them. The new sheets should not contain any button or macros.
hope u are getting my point.

[kishlaya]

any new solution for this??? guys i am really in a bad need for this solution... kindly help...

[mdmackillop]

Something like this, I think

Sub MakeSheets()
Dim arr
arr = Array("PDN CL", "Main Menu", "SEM", "BOM") 'etc.
For Each a In arr
Sheets(a).Visible = True
Sheets(a).Activate
Select Case a
Case "PDN CL"
ActiveSheet.Name = "ECO" & Sheets("SEM").Range("M2").Value & " PDN Checklist"
ActiveSheet.Visible = False

Case "Main Menu"

Case "SEM"
ActiveSheet.Name = "ECO" & Sheets("SEM").Range("M2").Value & " Supplier Acceptance"
ActiveSheet.Visible = False
Case "BOM"
'etc.

Next
End Sub

[kishlaya]

hi mdmackillop
i tried your code but its creating only the main menu sheet. nothing else other than that.

[kishlaya]

is dere a way i can call all these button procedures in a single button procedeure??
hope this vba community helps as i am still stuck in this....

[mdmackillop]

My code is not a complete solution but a suggested method. I'm leaving it to you to assess it and add the detail code for each case.

[kishlaya]

yes but when i executed yoour sheet excluding the main menu it was still genrating the main menu.

[kishlaya]

guys i think i am close.. I am able to save each file by click the corresponding output button. now i added one more button which will call all the buttons at once. some of the lines are being executed but then I get 'application or object defined error'. I dont know why such error is coming. here is the code which i gave to the new button
Private Sub CommandButton16_Click()
ThisWorkbook.Sheets("Main Menu").Visible = True
Call CommandButton10_Click
ThisWorkbook.Sheets("Main Menu").Visible = True
Call CommandButton8_Click
ThisWorkbook.Sheets("Main Menu").Visible = True
Call CommandButton5_Click
ThisWorkbook.Sheets("Main Menu").Visible = True
Call CommandButton9_Click
ThisWorkbook.Sheets("Main Menu").Visible = True
Call CommandButton6_Click
ThisWorkbook.Sheets("Main Menu").Visible = True
Call CommandButton7_Click
ThisWorkbook.Sheets("Main Menu").Visible = True
Call CommandButton11_Click
ThisWorkbook.Sheets("Main Menu").Visible = True
Call CommandButton13_Click
End Sub

[Bob Phillips]

Where was the error, which code line?

[kishlaya]

Hi
It is not showing any particular line, the debugger just says that application or object define error.

[Bob Phillips]

It must have highlighted a line, that is how the debugger works.

[kishlaya]

that is the point ...it jus gives an obejct defined error...it dusnot highlights any line...

[kishlaya]

gguys now i am getting an error
Runtime error '-214767259 (80004005)': Automation Error
any idea why it is showing the error????

i inserted a command button in each output sheet to save that particular sheet with the same name in a different work book. also it is needed that in the new workbook there should not be any button.
upto creating a new sheet and saving it is working fine but when it comes to deleting the button I am receiving this error.
Please help me I am really very close for the solution
here is my code for the button

Private Sub CommandButton3_Click()
MyfileName = Application.GetSaveAsFilename(ActiveSheet.Name & ".xls")
ActiveSheet.Select
ActiveSheet.Copy
ActiveSheet.SaveAs Filename:=ActiveSheet.Name & ".xls"
ThisWorkbook.Sheets(ActiveSheet.Name).Unprotect Password:=""
Application.CommandBars("Control Toolbox").Visible = True
ActiveSheet.Shapes("CommandButton1").Select
Selection.Delete
ActiveSheet.Shapes("CommandButton2").Select
Selection.Delete
ActiveSheet.Shapes("CommandButton3").Select
Selection.Delete
Application.CommandBars("Control Toolbox").Visible = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Close
end sub

Thanks and regards