Solved: Create files and change properties

[joshcas]

Hi,

I'm working on a very tricky macro and I'm running out of ideas.

Basically in column A you have all the names of the worksheets in the workbook.

Columns B to E you have the name of the file that it should generate (headers) and the property to make the sheets visible or invisible.

So the macro would generate new files (Copy the current file) (the new name would be the header) then it would open the new file or better yet closed and it would hide or show the tabs depending if it says show or range (Visible=True) but if it says hide, it should hide it .

The tricky part is that they could add more columns to the right to generate new files , so that would have to be dynamic, how we can do this ?

Any help would be much appreciated

10167

[patel]

paste here your code, or better attach a sample file

[mancubus]

below procedure creates and deletes a copy of the original workbook.
change "Sheet1" to your sheet that contains tab and file names


Sub ClonesOfMaster()
'http://www.vbaexpress.com/forum/showthread.php?t=46638

Dim fpath As String, fname As String, copyName As String
Dim i As Integer, j As Integer
Dim wbMstr As Workbook, wbCopy As Workbook

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set wbMstr = ThisWorkbook
With wbMstr
fpath = .Path & "\"
copyName = fpath & Left(.Name, (InStrRev(.Name, ".") - 1)) & "_copy.xlsm"
.SaveCopyAs copyName
End With

For j = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
fname = fpath & Cells(1, j)
Set wbCopy = Workbooks.Open(copyName)
With ActiveWorkbook
.SaveAs fname, 52
For i = 2 To wbMstr.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
If wbMstr.Worksheets("Sheet1").Cells(i, j) = "Hide" Then .Worksheets(Cells(i, 1).Value).Visible = xlSheetHidden
Next
.Save
.Close
End With
Next

Kill copyName
End Sub

[joshcas]

Brilliant solution Mancubus . Thank you so much , I really appreciate it

Hats off to you

[mancubus]

you are most welcome.
im glad it helped.

[Tom Jones]

Good job mancubus.

[mancubus]

Good job mancubus.

thanks tom jones... :)