jeudi 27 octobre 2016

Save new workbook from selected sheets

So my main goal is to save sheets (depending on if they are selected by a checkbox on the front page) to a new workbook.

I have 6 sheets, so I have 6 different checkboxes on my front page/sheet.

Here is my example code I've tried (Note: this only shows a check for 1 checkbox, however I require this for 6 checkboxes):

Sub saveSheetWorkbook()

Dim exampleName As Variant
Dim exampleSavePath As String
Dim exampleSheet As Variant

exampleName = InputBox("Who will this be sent to?")

exampleSavePath = ActiveWorkbook.Path & "\" & exampleName

If Worksheets("Example Worksheet 1").Range("E29") = True Then
exampleSheet = "Example Worksheet 2"
End If

Sheets(Array("Example Worksheet 1"), exampleSheet).Copy
ActiveWorkbook.SaveAs Filename:=exampleSavePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

End Sub

For example, I want to always save Example Worksheet 1, but only save Example Worksheet 2 (and 3,4,5,6) if the checkbox is ticked. The cell E29 in Example Worksheet 1 is the linked cell for the checkbox.

So this macro works when the checkbox is ticked, but when the checkbox is unticked, I get an error.

I have set it up so that the sheet array either contains the name or nothing. but when containing nothing, that gives me the error.

Any help would be great.




Aucun commentaire:

Enregistrer un commentaire