title: VBA macro to find Sheet count in a directory
body: <p>I'm not good with VBA at all but I was curious to know if there is a way to count the amount of worksheets in a workbook that's looped for all the files in a folder.</p>
  
  <p>For example, A1 list the file names and B1 shows the count of sheets.</p>
  
  <pre>
  A1       B1
  book1    5
  book2    6
  </pre>
  
  <p>currently have this code set up and need to adjust it</p>
  
  <pre><code>Sub ListAllFile()
  
  Dim objFSO As Object
  Dim objFolder As Object
  Dim objFile As Object
  Dim ws As Worksheet
  
  Set objFSO = CreateObject("Scripting.FileSystemObject")
  Set ws = Worksheets.Add
  
  Set objFolder = objFSO.GetFolder("W:\101g-19 (4.20.18) - Copy\")
  ws.Cells(1, 1).Value = "The files found in " &amp; objFolder.Name &amp; " are:"
  
  
  For Each objFile In objFolder.Files
      ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = objFile.Name
      'ADD A WORKSHEET AND PASTE "=SHEETS()" in A1 the copy value of a1 in to list
      'close files with out saving
  
  Next
  
  Set objFolder = Nothing
  Set objFile = Nothing
  Set objFSO = Nothing
  
  End Sub
  </code></pre>