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

 

[shg]

Welcome to the board.

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

 

[bdaman]

I swapped that part into my code, and the error changed, but I am still getting an error.

Now its Method "Copy" of object '_Worksheet' failed

 

[shg]

These are hidden sheets?

Perhaps they need to be unhidden first, copied, then rehidden. Can you do that?

 

[bdaman]

Yes. they are actually very hidden in the workbook. When I get back into the workbook in a few hours I will try that. I think you very much for your efforts.

 

[shg]

Actually, they can't all be hidden -- Excel requires at least unhidden worksheet.

 

[bdaman]

Correct. I have 1 sheet that never is hidden that displays information about the version level of the file (what template it was derived from). And a note about enabling macros to allow the rest to work. There are some other pages that never hide in the workbook. But the pages I want to export out would most possibly be hidden when the action is trying to take place.

 

[shg]

Then you should test the sheets before unhiding them, and re-hide only those that were previously.

 

[bdaman]

I have added a piece to unhide each page before copy out, and it seems to copy them out properly. It does not re hide them properly as I am asking it to, but that is a second concern. The next problem it is presenting is that there are some formulas in the workbook referring to named ranges. when it copies the first sheet out, no issues. The second sheet out is referencing the same named ranges. So it is asking if i want to use the same named range as is used in the newly exported sheet sheet. This isnt a horrible thing. Until it asks 20 questions per sheet across 50 sheets. It is possible to have the sh.copy command default answer "yes" each time?

 

[shg]

I dunno; you could try application.displayalerts = false, but I don't know if that will work since the question is coming from Excel, not VBA.