VBA Countif between tabs

[beginvbaanalyst]

Hi Everyone,

I'm trying to create a countif between two tabs, what's a short macro to do this?
I need it to be unlimited because I'll never know what the concatenate numbers. They always change and it's never the same.
Here is my recorded macro data:

Range("D2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],""-"",RC[-2])"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D1638")
    Range("D2:D1638").Select
    Sheets("REMOVE CLUB FROM ASST").Select
    Range("C2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],""-"",RC[-1])"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C1592")
    Range("C2:C1592").Select
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("ADD CLUB TO ASST").Select
    Range("E2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=COUNTIF('REMOVE CLUB FROM ASST'!C[-2],'ADD CLUB TO ASST'!RC[-1])"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E1638")
    Range("E2:E1638").Select

When I create the vba, do I just make active the sheet I want to use as my range, and again for my criteria?
Thank you for all the help.

 

[mse330]

Try the below code ...

Sub test()

Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("ADD CLUB TO ASST") 'change sheet name as needed 
Set ws2 = Sheets("REMOVE CLUB FROM ASST") 'change sheet name as needed 

With ws1.Range("A2", ws1.Range("A" & Rows.Count).End(xlUp))
   .Offset(, 3).Value = "=CONCATENATE(A2,""-"",B2)"
   .Offset(, 4).Value = "=COUNTIF('" & ws2.Name & "'!C:C,D2)"
End With

With ws2.Range("A2", ws2.Range("A" & Rows.Count).End(xlUp))
   .Offset(, 2).Value = "=CONCATENATE(A2,""-"",B2)"
End With

End Sub