Copy All Worksheets of a Certain CodeName to another workbook

[bdaman]

Hello, Long time lurker, first time asker. I have always been able to find old questions to figure out my own questions. But this one has me hitting a wall. I have a strong feeling it it so simple I am just not seeing it.

I have all the worksheets in my Workbook with code names set so that if the user renames a worksheet in the standard excel way, it will not break my code for hiding and un hiding the pages.

I need to be able to allow my users to export a certain grouping of sheets to a new workbook with a macro. The Code names for them is always along the same naming convention. NWBuilding1Equiptment, NWBuilding2Equiptment, SBuilding1Equiptment, SBuilding2Equiptment, ect. I want to make sure I keep this dynamic so as more pages get added, as long I keep them named properly in code names, the script still catches them.

My code is:

Sub testfilecreate()

Dim cwb As Workbook
Dim wbnew As Workbook
Dim sh As Worksheet
Dim cname As String


Set cwb = ThisWorkbook
Set newbook = Workbooks.Add


    Do
        fName = Application.GetSaveAsFilename(filefilter:="Excel Files (*.xlsx), *.xlsx")
    Loop Until fName <> False


newbook.SaveAs Filename:=fName


Set wbnew = ActiveWorkbook






For Each sh In cwb.Worksheets
    
    cname = vbNullString
    
    If sh.CodeName Like "*Build*" Then
        cname = sh.Name
        cwb.Sheets(cname).Copy after:=worksheets(wbnew).Sheets(1)
                
    End If
Next sh


End Sub

I get a mismatch type error when running this. I have tried to change cname=sh.codename, but still get an error. I have also taken out the worksheets() and left just the After:=wbnew.sheets(1)

If I comment out the copy task and have it just unhide the sheets I want exported, It works as expected. Asks for a new file name, creates the file where I tell it, then unhides the sheet in the existing workbook. I just need it to copy it out to the new workbook.

Thanks in advance

 

[bdaman]

Sorry for a late response. May things came up ahead of this. I threw in a Application.DisplayAlerts = False ahead of my expert code. then Application.DisplayAlerts = True at the end of the routine. It worked perfectly with no popups. Thank you very much for your input. Im pasting the working code below in case it can help others. I would cal this solved. Not to add in the testing for hidden or un hidden and I'll be golden.

Sub testfilecreate()

Dim cwb As Workbook
Dim wbnew As Workbook
Dim sh As Worksheet


Set cwb = ThisWorkbook
Set newbook = Workbooks.Add


Application.DisplayAlerts = False


    Do
        fName = Application.GetSaveAsFilename(filefilter:="Excel Files (*.xlsx), *.xlsx")
    Loop Until fName <> False


newbook.SaveAs Filename:=fName


Set wbnew = ActiveWorkbook




For Each sh In cwb.Worksheets


    If sh.CodeName Like "*Build*" Then
        sh.Visible = xlSheetVisible
        sh.Copy after:=wbnew.Sheets(1)
        sh.Visible = xlSheetVeryHidden
        
        
    End If
Next sh


Application.DisplayAlerts = True
End Sub