export selected sheets

[vbachanger]

Hi, my workbook cointains 5 worksheets of which I need to export sheet1, sheet2 and sheet3 in seperate workbooks.

Need to copy format, without formulas. The code below copies the user form button into the new workbook. How to prevent that?

Workbooks should have the same name as the original file and the sheet name. How to change the code to save the file with the name of the sheet without defining the sheetname in the code?

Sub sheet1_2_3 ()

strFile = ThisWorkbook.Fullname
strFile = Left(strFile, InStrRev(strFile, ".")-1) & "_sheet1.xlsx

Sheets("sheet1").Copy
With ActiveSheet.UsedRange
.Value = .Value
End With

ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=xlOpenXMLWorkbook, local:=True

End Sub

THX

 

[DanteAmor]

Try this:

Application.CopyObjectsWithCells = False

Sub sheet1_2_3()
  Dim strFile As String
  strFile = ThisWorkbook.FullName
  strFile = Left(strFile, InStrRev(strFile, ".") - 1) & "_sheet1.xlsx"
  Application.CopyObjectsWithCells = False
  Sheets("sheet1").Copy
  With ActiveSheet.UsedRange
  .Value = .Value
  End With
  ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=xlOpenXMLWorkbook, local:=True
  Application.CopyObjectsWithCells = True
End Sub

 

[DanteAmor]

Workbooks should have the same name as the original file and the sheet name. How to change the code to save the file with the name of the sheet without defining the sheetname in the code?

For that, try:

Sub sheet1_2_3()
  Dim strFile As String
  
  Application.CopyObjectsWithCells = False
  Sheets("sheet1").Copy
  With ActiveSheet.UsedRange
    .Value = .Value
  End With
  strFile = ThisWorkbook.FullName
  strFile = Left(strFile, InStrRev(strFile, ".") - 1) & "_" & ActiveSheet.Name & ".xlsx"
  ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=xlOpenXMLWorkbook, local:=True
  Application.CopyObjectsWithCells = True
End Sub

 

[vbachanger]

That is working! I've got other objects in the sheet, screenshots etc. I want to keep. Only the button should not be copied.

Do you know how to export the first three sheets to separate files?

Thank you!

 

[vbachanger]

Something like this maybe. Check the name of each worksheet and only those sheets that have a number as worksheet name should be exported to a new xlsx and saved.

Dim TexttoFind As String
TexttoFind = "isnumber"

For Each ws In ThisWorkbook.Sheets
    If InStr(1, ws.Name, TexttoFind, vbBinaryCompare) <> 0 Then
        ws.Copy

 

[DanteAmor]

Check that in the 3 sheets, the name of the button is the same. Replace in the macro "button1" by the name of your button.

Try this:

Sub sheet1_2_3()
  Dim strFile As String
  Dim i As Long
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  For i = 1 To 3
    ThisWorkbook.Sheets(i).Copy
    With ActiveSheet.UsedRange
      .Value = .Value
    End With
    On Error Resume Next
    ActiveSheet.DrawingObjects("button1").Delete
    On Error GoTo 0
    strFile = ThisWorkbook.FullName
    strFile = Left(strFile, InStrRev(strFile, ".") - 1) & "_" & ActiveSheet.Name & ".xlsx"
    ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=xlOpenXMLWorkbook, local:=True
  Next
End Sub