Incorrect number of worksheet counted?

[Eric Penfold]

This code counts only 4 Sheet rather than 50 sheets ??

Sub vLookupAnotherWorkbook()

    Dim Src As Workbook
    Dim Des As Workbook
    Dim SASheet As Worksheet
    Dim PASheet As Worksheet
    Dim MBefore As Integer
    Dim MName As String
    Dim ColName As Object
    Dim WSArray, i
    ReDim WSArray(1 To Sheets.Count)
   
    
    Dim FileToOpen As Variant
   
    When I try to count the worksheets it seems to only count rather than 51 sheets?


   [CODE=vba]With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    FileToOpen = ("\\DC01\Company\PURCHASING\Forecasting\Brett Martin Template for Vlookup.xlsm")
    Workbooks.Open FileToOpen
    Set Src = Workbooks("Brett Martin Template for Vlookup.xlsm")
    Set Des = Workbooks("Brett Martin Forecast 2022.xlsm")
    Set SASheet = Worksheets("Sales Analysis")
    Set PASheet = Worksheets("Purchasing Analysis")

    MBefore = Format(DateAdd("m", -1, Date), "mm")
   
    MName = MonthName(MBefore)
    
    For i = 1 To Sheets.Count
    WSArray(i) = Des.Sheets(i).Name
    Next


    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
   
  Src.Close False

     End Sub

 

[Fluff]

Which workbook are you trying to count the sheets in & which part of the code is returning the wrong count?

 

[Eric Penfold]

Hi

This workbook (Brett Martin Forecast 2022)

    Dim WSArray, i
    ReDim WSArray(1 To Sheets.Count)
    For i = 1 To Sheets.Count
    WSArray(i) = Des.Sheets(i).Name
    Next

 

[Fluff]

In that case you need to use

For i = 1 To Des.Sheets.Count

otherwise it's counting the sheet in the workbook you just opened.

 

[Eric Penfold]

Thanks very much

 

[Fluff]

You're welcome & thanks for the feedback.