Array question in excel Hi, I have a master workbook and distribute workbook for

Question

Array question in excel

Hi,

I have a master workbook and distribute workbook for people.

In this distribute workbook, I copied some worksheets from Master workbook.

In the Master, I made a button to do this.

Set v = ThisWorkbook.Sheets(Array("C", "S", "B", "PCheck ", "P Log", "IR", "M", "Start", "End")

This is the array that I used, but the problem is if I add a sheet or sheets, then these new sheets are not going to be included in distribute workbook.

Because the new sheets that I add are not in array. I can't go the VBE and edit this array everytime some sheets added.

I don't know how many sheets I will add, but I do know that some sheets are not included in Master.

I want to exclude, 'Rates" and "Product" sheets.

This will not be in distribute workbook.

Thanks!

Here is my code;

Sub CopyActiveSheet()      
    Dim str_folder As String
    Dim wname As String
    Dim v As Variant '<--- my array
    Dim mywb As Workbook
    Dim fname As Variant
   
    Dim Newwb As Workbook
    Dim NewwbName As String
    Dim FileFormatValue As Long
    Dim wbD As Workbook
    Dim z As Long
    Dim hh As Long
    Dim k As String
     
     
     Dim Sh As Worksheet
     Dim oleObj As OLEObject
     Dim ShapeR As Shape
    
     Dim Ans As Integer
     Dim wr As String
    
    Dim E As Long, t As Long
    Dim St As Long
    Set mywb = ThisWorkbook
    Set v = ThisWorkbook.Sheets(Array("C", "S", "B", "PCheck ", "P Log", "IR", "M", "Start", "End")

    wname = ThisWorkbook.Name
       'Debug.Print wname

    Application.ScreenUpdating = False
Application.DisplayAlerts = False

Ans = MsgBox("Did you protect ?", vbYesNo, "To Create")
If Ans = vbNo Then
    ThisWorkbook.Sheets("IR").Activate
Else
       
        fname = "c:"
        'Debug.Print fname

        'Find the correct FileFormat that match the choice in the "Save as type" list
       

      
                'Copies the ActiveSheet to new workbook
               
                v.Copy '<--- here is my array
               
                 Set Newwb = ActiveWorkbook
             
                
    
              
                Newwb.SaveAs fname, FileFormat:=FileFormatValue, CreateBackup:=False
             '   Debug.Print Newwb.Name               
                
                Newwb.Close False
              
                Set Newwb = Nothing
            End If
            
             
        End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Explanation / Answer

.As you wish to create a distribution copy workbook with all of the original sheets except for the Rates and Product sheets, try the following approach:"

'==========>>
Option Explicit

'---------->>
Public Sub Tester()
    Dim srcWB As Workbook, destWB As Workbook
    Dim SH As Worksheet
    Dim arrDelete As Variant
    Dim sPath As String
    Const sDeleteSheets As String = "Rates,Product"
    Const sFilename As String = "TobemasterDistribution.xlsx"       '<<=== Change

    Set srcWB = ThisWorkbook
    arrDelete = Split(sDeleteSheets, ",")

    With Application
        sPath = .DefaultFilePath & .PathSeparator
    End With

    With srcWB
        .Save
        On Error GoTo XIT:
        Application.DisplayAlerts = False
        .Sheets(arrDelete).Delete
        .SaveAs Filename:=sPath & sFilename, FileFormat:=51          '<<==== Change
    End With
XIT:
Application.DisplayAlerts = True
End Sub
'<<==========

If you wish to include macros in the distribution workbook, change

"TobemasterDistribution.xlsx"

to

"TobemasterDistribution.xlsm"

and change

.SaveAs Filename:=sPath & sFilename, FileFormat:=51

to

.SaveAs Filename:=sPath & sFilename, FileFormat:=52

The original workbook is saved with all of the original sheets..,

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.