Hello I have for sheets in a workbook with each sheet having its own query table from MySQL. These sheets also have some user form button on them like refresh, Go to first row etc. They also have few calculated columns.
I want to create a Export button that makes a copy (paste only values) of the entire workbook without the data connections and form buttons.
The code below will run through all existing sheets in your workbook, it will copy the entire sheets cells (values only, without Form Controls or links) , and will paste them in the same structure to another workbook that is saved on the Desktop.
Sub ExportCleanDataSheets() Dim Sht As Worksheet Dim DestSht As Worksheet Dim DesktopPath As String Dim NewWbName As String Dim Wb As Workbook Dim i As Long Set Wb = Workbooks.Add ' set path to Dektop DesktopPath = "C:\Users\" & Environ("USERNAME") & "\Desktop\" ' modify the name (I used "Clean_SQL_Data" and current date and time) to your needs ' i like to use the full date and time format, this way I don;t overwrite old files NewWbName = "Clean_SQL_Data " & Format(Now, "yyyy_mm_dd _hh_mm_ss") i = 1 ' scroll through all sheets and copy the values only to the new workbook For Each Sht In ThisWorkbook.Sheets If i <= Wb.Sheets.Count Then Set DestSht = Wb.Sheets(i) Else Set DestSht = Wb.Sheets.Add End If Sht.Cells.Copy With DestSht .Cells.PasteSpecial (xlPasteValues) .Cells.PasteSpecial (xlPasteFormats) .Name = Sht.Name End With i = i + 1 Next Sht Application.DisplayAlerts = False Wb.SaveAs Filename:=DesktopPath & NewWbName, FileFormat:=xlNormal Wb.Close Application.DisplayAlerts = True End Sub