Macros for counting the record count of each sheet

Closed
BabuSampath Posts 1 Registration date Wednesday September 16, 2015 Status Member Last seen September 16, 2015 - Sep 16, 2015 at 05:26 AM
Hi ,

Anybody plz help me in writing macros to prepare summary of records

i will be uploading txt files into excel each file will have header and trailer records
and in trailer record i will be having total number of record of that file.

so i now i need to add a new sheet ie summary sheet with the file name (sheet name) and record count of that sheet.

For each file loaded, the last record is the total count of records in that file. E.g. :


I need pull that record count (in example below = 119822) and the worksheet name (in example below = CUSTCBACCTSTSHIST) into a separate worksheet name Sum_Totals (e.g. below) ? ...repeat for all the files loaded and populate the Sum_Totals worksheet.

The output should be like
S.no Filename Record count
1 CUSTCBACCTSTSHIST 1198220

I used the below code but still getting error when the record count is more than 30000

Sheets.Add.Name = "Summary"
Z = 0
Sheets("Summary").Select
[a1] = "File Name"
[b1] = "Row count"
Range("a2").Select
While Z < i
Selection.Value = sheetName(Z)
ActiveCell.Offset(0, 1).Value = RowCount1(Z) - 3
ActiveCell.Offset(1, 0).Select
Z = Z + 1
Wend
End Sub