LearnExcel&VBA
Member
I want to run that code on a folder have multiple files so it count the used range of each workbook sheets any help would be appreicated.
Code:
Sub CountSheet()
Dim xFd As FileDialog
Dim xFdItem As String
Dim xFileName As String
Dim wbk As Workbook
Dim sht As Worksheet
Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
If xFd.Show Then
xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
Else
Beep
Exit Sub
End If
'''''''''''''''''''''Code
Dim wsSummary As Worksheet
Dim ws As Worksheet
Dim noRows&
Dim newRow&
Sheets.Add(Before:=Sheets(1)).Name = "Summary"
Const rowsCounterColumn$ = "I"
Const wsSummaryMainCol$ = "A"
Const wsHeaderRow% = 1
Set wsSummary = Worksheets("summary")
data clearance
wsSummary.Range("A1:B" & Cells.Rows.Count).ClearContents
Search worksheets and count rows based on set column
For Each ws In Worksheets
If ws.Name <> wsSummary.Name Then
noRows = ws.Range(rowsCounterColumn & Cells.Rows.Count).End(xlUp).Row - wsHeaderRow
newRow = wsSummary.Range(wsSummaryMainCol & 1).CurrentRegion.Rows.Count + 1
wsSummary.Cells(newRow, wsSummaryMainCol) = ws.Name
wsSummary.Cells(newRow, wsSummaryMainCol).Offset(, 1) = noRows
End If
Next ws
Set wsSummary = Nothing
'''''''''''''''''''''''''''
wbk.Close SaveChanges:=True
xFileName = Dir
Loop
End Sub