Title: Count the number of sheets in a separate workbook and return to a cell in original workbook
Tags: vba;excel
Question: I have written a query that opens a separate file, counts all the unique 13 digit values, and copies all of the data related to that no. into separate sheets in a new workbook. What I now need to do is, from the original workbook where the macro lives, count all of the the sheets in the new workbook and return the count to a cell in the original workbook. For some reason, this baffling me so any assistance would be greatly appreciated. 

```Option Explicit

Sub MPANSeparation()

Dim X As Integer               'Holds Count of rows
Dim Y As Integer            'Holds the count of copied cells
Dim MyLimit As Long         'Holds the count of matches
Dim MyTemp As String          'Holds the MPAN #
Dim MyNewBook As String     'Holds the name of the new workbook
Dim FullFileName As String  'Holds the full file name
Dim FileLocation As String  'Holds the file location
Dim FileName As String      'Holds the file name
Dim MPANSeparate As Excel.Workbook
Dim NumberOfSheets As Double

'Turn Off Screen Updates
Application.ScreenUpdating = False
'Turn off calculations
Application.Calculation = xlCalculationManual

'Identifies cell references for upload file
FullFileName = Sheet1.Cells(7, 2)
FileLocation = Sheet1.Cells(8, 2)
FileName = Sheet1.Cells(9, 2)

'Identifies workbook where data is being extracted from.
 Application.EnableEvents = False
Application.DisplayAlerts = False
Set MPANSeparate = Workbooks.Open(FullFileName, ReadOnly:=False)

'Ensure we're on the data sheet
Sheets("Sheet1").Select

'Get the count of the rows in the current region
X = Range("A1").CurrentRegion.Rows.Count


'Add a new "Scratch" Sheet after first sheet
Sheets.Add After:=Sheets(1)
'Rename newly added sheet
ActiveSheet.Name = "Scratch"

'Copy all of column A of the first sheet to scratch
Sheets(1).Range("A1:A" &amp; X).Copy Sheets("Scratch").Range("A1")

'Copy all of column B of the first sheet to scratch
Sheets(1).Range("B1:B" &amp; X).Copy 
Sheets("Scratch").Range("A1048575").End(xlUp).Offset(1, 0)

'Copy all of column C of the first sheet to scratch
Sheets(1).Range("C1:C" &amp; X).Copy 
Sheets("Scratch").Range("A1048575").End(xlUp).Offset(1, 0)


'Remove all duplicates
ActiveSheet.Range("$A:$A").RemoveDuplicates Columns:=1, Header:= _
    xlYes

'Select start of range
Range("A1").Select

'Loop to test for len of 13 characters
Do While ActiveCell.Value <&gt; ""
    'Logical test (is this cell 13 characters long)
    If Len(ActiveCell.Value) <&gt; 13 Then
        'Delete the whole row
        ActiveCell.EntireRow.Delete
    Else
        'Move down a cell
        ActiveCell.Offset(1, 0).Select
    End If
Loop

'Add CountIf formulas to column B (checking A,B &amp; C)
Range("B1:B" &amp; Range("A1048575").End(xlUp).Row) _
    .Formula = "=COUNTIF(Sheet1!C[-1]:C[1],Scratch!RC[-1])"

'Add a new workbook
Workbooks.Add
'Get the name of the new workbook
MyNewBook = ActiveWorkbook.Name

'Go back to this workbook
MPANSeparate.Activate

'Select start of range
Range("A1").Select

'Loop to add sheets (one for each MPAN)
Do While ActiveCell.Value <&gt; ""
    'Get MPAN #
    MyTemp = ActiveCell.Value
    'Add new sheet to "MyNewBook"
    Workbooks(MyNewBook).Sheets.Add _

After:=Workbooks(MyNewBook).Sheets(Workbooks(MyNewBook).Sheets.Count)
    'Rename newly added sheet to MPAN #
    Workbooks(MyNewBook).Sheets(Workbooks(MyNewBook).Sheets.Count).Name = 
MyTemp
    'Move down a cell
    ActiveCell.Offset(1, 0).Select
Loop

'Select start of range
Range("A1").Select


'The outer copy and paste loop
Do While ActiveCell.Value <&gt; ""

    'Select start of range
    Range("A1").Select

    'Get the first value we're looking for
    MyTemp = ActiveCell.Value
    'Get the actual count of matches
    MyLimit = ActiveCell.Offset(0, 1).Value


    'Go to the data sheet
    Sheets("Sheet1").Select

    'The A loop
    'Select start of range
    Range("A1").Select

        Do While ActiveCell.Value <&gt; ""
            If ActiveCell.Value <&gt; MyTemp Then
                'Move down a cell
                ActiveCell.Offset(1, 0).Select
            Else
                'Copy the entire row to the appropriate sheet in the new 
Workbook
                ActiveCell.EntireRow.Copy _

Workbooks(MyNewBook).Sheets(MyTemp).Range("A1048575").End(xlUp).Offset(1, 0)

                'Move down a cell
                ActiveCell.Offset(1, 0).Select

                'Increase Y by 1
                Y = Y + 1

                'If we have all the matches, add headings and go to 
NextOuterLoop
                If Y = MyLimit Then
                    Range("A1").EntireRow.Copy 
Workbooks(MyNewBook).Sheets(MyTemp).Range("A1")
                    GoTo NextOuterLoop
                End If
            End If
        Loop

    'The B loop
    'Select start of range
    Range("B1").Select

        Do While ActiveCell.Value <&gt; ""
            If ActiveCell.Value <&gt; MyTemp Then
                'Move down a cell
                ActiveCell.Offset(1, 0).Select
            Else
                'Copy the entire row to the appropriate sheet in the new 
Workbook
                ActiveCell.EntireRow.Copy _

Workbooks(MyNewBook).Sheets(MyTemp).Range("A1048575").End(xlUp).Offset(1, 0)

                'Move down a cell
                ActiveCell.Offset(1, 0).Select

                'Increase Y by 1
                Y = Y + 1

                'If we have all the matches, add headings and go to 
NextOuterLoop
                If Y = MyLimit Then
                    Range("A1").EntireRow.Copy 
Workbooks(MyNewBook).Sheets(MyTemp).Range("A1")
                    GoTo NextOuterLoop
                End If
            End If
        Loop


    'The C loop
    'Select start of range
    Range("C1").Select

        Do While ActiveCell.Value <&gt; ""
            If ActiveCell.Value <&gt; MyTemp Then
                'Move down a cell
                ActiveCell.Offset(1, 0).Select
            Else
                'Copy the entire row to the appropriate sheet in the new 
Workbook
                ActiveCell.EntireRow.Copy _

Workbooks(MyNewBook).Sheets(MyTemp).Range("A1048575").End(xlUp).Offset(1, 0)

                'Move down a cell
                ActiveCell.Offset(1, 0).Select

                'Increase Y by 1
                Y = Y + 1

                'If we have all the matches, add headings and go to 
NextOuterLoop
                If Y = MyLimit Then
                    Range("A1").EntireRow.Copy 
Workbooks(MyNewBook).Sheets(MyTemp).Range("A1")
                    GoTo NextOuterLoop
                End If
            End If
        Loop

NextOuterLoop:

    'Reset Y
    Y = 0
    'Go to the scratch sheet
    Sheets("Scratch").Select
    'Delete the entire row
    Range("A1").EntireRow.Delete

Loop

'Turn off display alerts
Application.DisplayAlerts = False
'Delete the scratch sheet
Sheets("Scratch").Delete
'Turn on display alerts
Application.DisplayAlerts = True

Workbooks(MyNewBook).SaveAs ("C:\Users\XNEID\Desktop\Test MPAN Destination 
Folder\Shell_MPANs_Test1" &amp; ".xlsx")


'Ensure we're back on the data sheet
Sheets("Sheet1").Select
'Select start of range
Range("A1").Select

Call forEachWs
'Turn On Calculations
Application.Calculation = xlCalculationAutomatic
'Turn on screen updates
Application.ScreenUpdating = True

End Sub

Sub forEachWs()
Dim ws As Worksheet

'Opens new workbook for formatting
Workbooks.Open "C:\Users\XNEID\Desktop\Test MPAN Destination 
Folder\Shell_MPANs_Test1.xlsx"

For Each ws In ActiveWorkbook.Worksheets
Call resizingColumns(ws)
Next
End Sub

Sub resizingColumns(ws As Worksheet)
With ws
    .Range("A1:BB1").EntireColumn.AutoFit
End With

NumberOfSheets = Workbooks(FileName).Worksheets.Count


End Sub 
```

Comment: Why not just go with `ThisWorkbook.Worksheets("SheetnameWhereCountIsIn").Range("A1").Value = Workbooks(FileName).Worksheets.Count` ? So write it in a cell in a sheet as requested instead of in the "NumberOfSheets" variable in the very end?

Comment: Just after you open the file,  Debug.Print MPANSeparate.Worksheets.Count

Here is the accepted answer: The following script opens a workbook and returns the count of sheets in Range A1 in the first sheet of the workbook the macro resides in:

```Sub Test()
Dim fullPath As String
Dim wb As Workbook

fullPath = "Somepath\someworkbook.xlsx"

Set wb = Workbooks.Open(fullPath)

ThisWorkbook.Worksheets(1).Range("A1").Value = wb.Worksheets.Count

wb.Close

Set wb = Nothing
End Sub
```

Comment for this answer: You should replace "Worksheets(1)" with the worksheet you wish to put the value in, e.g. `Worksheets("SomeNameHere")` and the Range with the target cell. - It should thus be `ThisWorkbook.Worksheets("Sheet14").Range("J10").value = wb.Worksheets.Count`
Comment for this answer: Is the sheet name "Sheet14" (the name of the tab in the workbook?) or something else? If it's something else, use the tab name. So `ThisWorkbook.Worksheets("Tab name").Range("J10").value`
Subscript out of range means you do not have a sheet named "Sheet14" at the moment. In the VBA Editor you'll see the "Tab name" between the brackets after "Sheet14" most likely.
Read up on accessing sheets in different ways here http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm
Comment for this answer: No worries, that's why we're here.
Comment for this answer: Thank you for your answer. When I add this in and step through the routine and get to the line ThisWorkbook.Worksheets(1).Range("A1").Value = wb.Worksheets.Count I can see the count in the routine but it doesn't put the count in any cells. The sheet on the original workbook where the button is and where I would like to return the result is Sheet 14 and cell J10 in an ideal world. Am I missing something obvious?
Comment for this answer: I have updated as per your suggestion but now on the same line creates a 'Runtime error 9 : Subscript out of range'. Any ideas?
Comment for this answer: It has worked. Thank you kindly for your assistance.


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 2, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2017:00:06/05/17', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<vba><excel>', 'Title': 'Count the number of sheets in a separate workbook and return to a cell in original workbook', 'ViewCount': 692, 'id': 44370514, 'user_AboutMe': '<p>By Day: I am a progressive SharePoint Developer/Analyst!</p>\n\n<p>By Night: I am a writer, actor, musician and generally all round nice guy!</p>\n\n<p>For Fun: I enjoy nothing more than a cheeky bit of Xbox and good Netflix boxset :)</p>\n', 'user_AccountId': 10953606.0, 'user_DisplayName': 'Dyhouse', 'user_Reputation': 93.0, 'user_Views': 20.0, 'user_id': 8049163.0}}