Moving some Worksheets to their Own Workbooks

[markf5998]

I have a workbook that has about 100 sheets, some of which are called "Fact Sheet 1", "Fact Sheet 2"..."Fact Sheet 50"

I'm trying to make each "Fact Sheet" its own file *if* cell C11 on the sheet contains any text.

I have written code to do it one sheet at a time, but the problem is when the new workbook is created, the original containing the other 49 sheets I want to re-save is closed.

It would be nice if this could all happen at once. Any ideas to get me going in the right direction?

 

[mikerickson]

Try this

Dim oneSheet as WorkSheet

For each oneSheet in ThisWorkbook.Worksheets
    With oneSheet
        If .Name Like "Fact Sheet *" And Len(CStr(.Range("C11"))) > 0 Then
            oneSheet.Copy
        End If
    End With
Next oneSheet

 

[markf5998]

Thanks for the reply!

I'm still a beginner when it comes to VBA, so a question, if you don't mind:

I can see what it's doing and substitute my "save sheet as new workbook" code in place of "onesheet.copy", but how do I tell it to just create the new file and not colse the original? Currently, it creates the new workbook, makes it the active workbook, and closes the original file. It looks like your code will work, as long as the original file stays open.

Thanks again for your help!

 

[mikerickson]

Actualy the code I posted does NOT save the newly made workbooks. To do that, one would need to know where to save it. If you aren't picky about that, you could use this, if you were picky, you could use SaveAs and specify a filepath.

Dim oneSheet as WorkSheet

For each oneSheet in ThisWorkbook.Worksheets
    With oneSheet
        If .Name Like "Fact Sheet *" And Len(CStr(.Range("C11"))) > 0 Then
            oneSheet.Copy
            ActiveWorkbook.Save
            ActiveWorkbook.Close
        End If
    End With
Next oneSheet

 

[markf5998]

Thanks! This gets me going in the right direction!