Loop through checkboxes and export worksheets

vbachanger

New Member
Joined
Sep 15, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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!


VBA Code:
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,691
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:
Upvote 0

vbachanger

New Member
Joined
Sep 15, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,691
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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
 
Upvote 0

vbachanger

New Member
Joined
Sep 15, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Thank you! - Works now with some adjustments:

Excel Formula:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,186,997
Messages
5,961,009
Members
438,514
Latest member
AngSP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top