CODEWITHSUNDEEP
vbaexcelmacroscounter
Ridwan
Ridwan

Reputation: 47

Excel VBA: Counting Data in Column from Another Workbook and Inputting Counter in Master Workbook

I need to create a macro in my CountResults.xlsm (Master Workbook) that solves the following problem. I have a column of data in another worksheet with either YES or NO. I need to come up with a macro that counts the amount of "YES" in the column. The column is located in Sheet2 of the workbook Test01.xlsx. Then take that count and put it in one cell in my CountResults.xlsm file. Like so:

YESorNO

I have a code that displays a count for a column in the same sheet. But this code does not count when there are 'breaks' in the column (empty spaces) like I have in my picture. This is that code:

Private Sub CommandButton1_Click()

MsgBox Range("A1").End(xlDown).Row
Range("A1").End(xlDown).Offset(1, 0).Select

End Sub

I have another code that helps with accessing another workbook and defining values for each workbook and worksheet:

Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim shSource As Worksheet
Dim shTarget As Worksheet

Set wbSource = Workbooks.Open(Filename:="C:\Users\khanr1\Desktop\Test_Excel\Test03.xlsm", ReadOnly:=True)
Set wbTarget = ThisWorkbook
Set shSource = wbSource.Worksheets("Sheet2")
Set shTarget = wbTarget.Worksheets("Sheet1")

Upvotes: 0

Views: 6749

Answers (1)

Zac
Zac

Reputation: 1944

Use COUNTIF. It will give you the total even if the range is in another workbook. i.e. =COUNTIF([Book2.xlsx]Sheet2!$D$2:$D$9, "Yes"). Problem with having COUNTIF within your sheet as a formula is that you will need to open the other workbook if you want the count to be update. Below VBA code will perform an update for you. Assign the sub to a button in your CountResults.xlsm workbook

EDIT: Added row count as per OP's requirement

Sub UpdateResults()

    Dim oWBWithColumn As Workbook: Set oWBWithColumn = Application.Workbooks.Open("<your Test01.xlsx address here>")
    Dim oWS As Worksheet: Set oWS = oWBWithColumn.Worksheets("Sheet2")
    Dim intLastRow as Integer: intLastRow = oWS.Cells(Rows.Count, "B").End(xlUp).Row

    ThisWorkbook.Worksheets("<name of the sheet in your CountResults.xlsm workbook>").Range("<cell address>").Value = Application.WorksheetFunction.CountIf(oWS.Range("B2:B" & intLastRow), "yes")

    oWBWithColumn.Close False

    Set oWS = Nothing
    Set oWBWithColumn = Nothing

End Sub

Upvotes: 1

Related Questions