Loop through checkboxes and export worksheets

[vbachanger]

Hi guys,

I need a code that loops through 7 different chechboxes and if checked, runs the code below.

The code below exports every numeric worksheet name - I need only to export worksheets which are checked.

Thank you!

If UserForm1.CheckBox.Value = True Then
For i = 1 To 7
    UserForm1.Controls("CheckBox" & i).Value = True
    Next i
    End If

Dim Sh As Worksheet
Dim j As Integer
Dim strFile As String

Application.CopyObjectsWithCells = True 

    For Each Sh In ThisWorkbook.Sheets 
        For j = 1 To Len(Sh.Name)
            If IsNumeric(Mid(Sh.Name, j, 1)) Then
            Sh.Copy
             With ActiveSheet.UsedRange
             .Value = .Value
              strFile = ThisWorkbook.FullName
              strFile = Left(strFile, InStrRev(strFile, ".") - 1) & "_" & ActiveSheet.Name & ".xlsx" 
              ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=xlOpenXMLWorkbook, local:=True
              ActiveWorkbook.Close
         End With
        Exit For
     End If
    Next j
  Next Sh
End Sub

 

[Micron]

Not sure I understand. First your code sets 7 checkboxes to True but does nothing with that result, so how does this apply:

I need a code that loops through 7 different chechboxes and if checked

Since all the checks are now True, why do you think there is any doubt about which ones might not be checked?
Is there something about the checkboxes that tells you which workbooks to "export"? That code does a Save As, not exports? And only if IsNumeric is true when a portion of the file name is passed to that function?

I'm thinking you got this code from somewhere and you think it's close to what you want but doesn't do what you want. I'd say it's not that close, but again, but the situation is not quite clear. Also, I think some examples of the sheet names would help.

 

[vbachanger]

I have 10 worksheets, of which are 7 with numeric file name. There are 7 checkboxes in a userform and let's say checkbox 1, 2 and 5 are checked, then save each worksheet (1, 2 and 5) as seperate xlsx.

 

[Micron]

Sorry, still too vague for me to help much. Questions asked, not answered - so maybe like this, where 7 checkboxes (named like Checkbox1 and so on) are on userform1 and the sheets are named 1 or 2 or 3 and so on.

Dim wb As Workbook
Dim i As Integer

Application.CopyObjectsWithCells = True 
For i = 1 To 7
  If Userform1.Checkbox & i = -1 Then
    Set wb = Workbooks.Add
    ThisWorkbook.Sheets(i).Copy Before:=wb.Sheets(1)
    wb.SaveAs "FilePathGoesHere" 
  End If
Set wb = Nothing
Next

 

[vbachanger]

Thank you! - Works now with some adjustments:

Dim i As Integer
Dim strFile As String

Application.CopyObjectsWithCells = True
  For i = 1 To 7
  If UserForm1.Controls("CheckBox" & i) = -1 Then
  ThisWorkbook.Sheets(i).Copy
  
With ActiveSheet.UsedRange
             .Value = .Value
              strFile = ThisWorkbook.FullName
              strFile = Left(strFile, InStrRev(strFile, ".") - 1) & "_" & ActiveSheet.Name & ".xlsx"
              ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=xlOpenXMLWorkbook, local:=True
              ActiveWorkbook.Close
         End With
        Exit For
     End If
  Next
End Sub

 

[Micron]

Glad you were able to solve it.