VBA code to ungroup worksheets

Hi All,

Excuse my VBA, its a bit rusty. But i wrote a code, to copy worksheets from one workbook, to another workbook, that gets sent out to a mailing list. The issue is after doing so, the original workbook, groups all the tabs. I would like your help to amend the code to ungroup the tabs in the original workbook.

I think the issue is in the initial sheets(array()) line which initially groups it

Thanks


Here is the code I have:

Sub ExportNewFile()

On Error GoTo errorHandler
' '*****Part 1: Define location to save the exported files***

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
Dim NewName As String

    thisAppwin = Application.ActiveWorkbook.Name
    strpath = Application.ActiveWorkbook.Path & "\"
    cdir = Left(strpath & thisAppwin, Len(strpath) + Len(thisAppwin) - 5)
    NewName = cdir & "_EXPORT" ' & thisAppwin
    
    
    Sheets(Array("DATA", "UK", "EU7", "EU28", "GLOBAL" _
        , "PRINT")).Select
  
    Sheets(Array("DATA", "UK", "EU7", "EU28", "GLOBAL" _
        , "PRINT")).Copy
    

        ActiveWorkbook.SaveAs Filename:= _
        NewName & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
       

    Sheets("PRINT").Activate
    ActiveSheet.Buttons.Delete
    Sheets("DATA").Activate
    ActiveSheet.Buttons.Delete
    
   Range("I1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Range("A1").Select
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    ActiveWorkbook.Save
   MsgBox ("Your Excel file to send out is in the same folder; " & NewName), vbOKOnly
 Exit Sub

errorHandler:
   MsgBox ("There has been an error. Please contact IT for support")
   
End Sub

Hi,

Selecting a sheet that hasn't been grouped will remove the grouping. e.g. Sheet1.Select

Originally Posted by Richard Buttrey

Hi,

Selecting a sheet that hasn't been grouped will remove the grouping. e.g. Sheet1.Select

1. The problem is that all the worksheets on the original workbook are grouped, so i cannot select a worksheet.

2. For another workbook i have where i do have a non grouped worksheet, selecting it is fine, it ungroups it, but via VBA i have an issue as activating the original workbook due to the filename being dynamic becomes troublesome. Below is the code i used, to activate the original workbook but doesnt seem to work

Dim w As workbook 
Set w=ActiveWorkbook 
 ....

w.Activate

Would you be kind to tell me what code will solve this?

Regards

You may omit select:

Sub ExportNewFile()

On Error GoTo errorHandler
' '*****Part 1: Define location to save the exported files***

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
Dim NewName As String

    thisAppwin = Application.ActiveWorkbook.Name
    strpath = Application.ActiveWorkbook.Path & "\"
    cdir = Left(strpath & thisAppwin, Len(strpath) + Len(thisAppwin) - 5)
    NewName = cdir & "_EXPORT" ' & thisAppwin
    
      
    Sheets(Array("DATA", "UK", "EU7", "EU28", "GLOBAL" _
        , "PRINT")).Copy
    

        ActiveWorkbook.SaveAs Filename:= _
        NewName & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
       

    Sheets("PRINT").Activate
    ActiveSheet.Buttons.Delete
    Sheets("DATA").Activate
    ActiveSheet.Buttons.Delete
    
   Range("I1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Range("A1").Select
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    ActiveWorkbook.Save
   MsgBox ("Your Excel file to send out is in the same folder; " & NewName), vbOKOnly
 Exit Sub

errorHandler:
   MsgBox ("There has been an error. Please contact IT for support")
   
End Sub

You may also simply use:

Sheets("DATA").Select

to deselect all other sheets.