Dear Experts,
I need a code which count the particular column across multiple sheets that how many rows have been used in Column "G".
I have appropriate data in column "G" across multiple sheets so i just want that when i use the code Then all sheets count should be showed on new sheet which name would be "SUmmary"
Since you did not provide specific details nor a sample document, I am guessing at your actual needs.
Option Explicit
Sub CountG()
Dim ws As Worksheet, s1 As Worksheet
Set s1 = Sheets("Summary")
Dim lr As Long, x As Long
x = 0
For Each ws In Worksheets
If ws.Name <> "Summary" Then
lr = ws.Range("G" & Rows.Count).End(xlUp).Row
x = x + lr
End If
Next ws
s1.Range("A1") = "Total Rows = " & x
End SubThank you but its giving error.
Subscript out of range
By the way thank you i have sort out the issue if you could help me further that i want separately each sheet total along with sheet name.
Sub CountG()
Dim ws As Worksheet, s1 As Worksheet
Set s1 = Sheets("Summary")
Dim lr As Long, x As Long, lrS As Long
'x = 0
For Each ws In Worksheets
If ws.Name <> "Summary" Then
lr = ws.Range("G" & Rows.Count).End(xlUp).Row
'x = x + lr
End If
lrS = s1.Range("A" & Rows.Count).End(xlUp).Row
Range("A" & lrS + 1) = ws.Name
Range("B" & lrS + 1) = lr
Next ws
's1.Range("A1") = "Total Rows = " & x
End Subcrossposted: mrexcel.com/board/threads/code…ication-required.1133725/
Your post does not comply with our Forum RULES. Use code tags around code.
Posting code between tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Highlight your code and click the </> icon at the top of your post window.
(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
Why do you need VBA for this?
I said why?
Attach a small example workbook.
Because it is related to my work i often count these sheets via CountA formula i know it can be done with single click which can save my time but do not know how to make this vba.
You don't need VBA. Create a summary sheet and list the sheet names in a column. Then use a combination of COUNTA and INDIRECT
=COUNTA(INDIRECT("'"&D4&"'!G:G"))-1
Thank you so much roy.
Pleased to help
Don’t have an account yet? Register yourself now and be a part of our community!