Creating macro in excel

0 votes

Please assist me in designing a macro that searches several workbook sheets for a specific column and displays the number of values in that column in a new sheet along with the sheet name.

I have 3 pages that are labelled A, B, and C. I tried the code below, however, it is not calculating based on the name of the column header.

I have columns like Company Company Company ABC ABC ABC DEF GHI
JKL

Wanted Output: Sheet name Count A 1 B 3 C 2

Sub Click() Dim sh As Worksheet, ws As Worksheet, LstRw As Long, x, s As String Dim rng1 As Range

Set ws = Sheets.Add
ws.Name = "Report"
s = "A"
For Each sh In Sheets
    If sh.Name <> ws.Name Then
        With sh

              x = Application.WorksheetFunction.CountA(.Range("A:A"))
            With ws
                LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                .Cells(LstRw, 1) = sh.Name
                .Cells(LstRw, 2) = x

            End With
        End With
    End If
Next sh

End Sub
Oct 10, 2022 in Others by Kithuzzz
• 38,010 points
284 views

1 answer to this question.

0 votes
The Macro Recorder is used to record macros, and Excel uses your actions to generate VBA code that you may later edit as necessary.

See:
MSDN : Recording a Macro to Generate Code, and,
MSDN : Revising Recorded Visual Basic Macros.
answered Oct 11, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

Convert Feet to meters in excel with macro for entire column

You can convert Feet to Kilometers using ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 63,700 points
478 views
0 votes
1 answer

Creating a function in excel VBA to calculate the average point in a circular set of numbers

I used the following code to determine ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,700 points
894 views
0 votes
0 answers

Creating a chart in Excel that ignores #N/A or blank cells

A dynamic data series will be used ...READ MORE

Nov 2, 2022 in Others by Kithuzzz
• 38,010 points
193 views
0 votes
1 answer

How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,700 points
717 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
955 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,287 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
578 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
812 views
0 votes
1 answer

Creating an unique export data VBA Macro Function in Excel

Unfortunately, the tables will vary in size. ...READ MORE

answered Nov 14, 2022 in Others by narikkadan
• 63,700 points
554 views
0 votes
1 answer

Insert pie chart in Excel macro function

Think about arranging your data in a ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,700 points
936 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP