put results on worksheets in another workbook

[helpexcel]

I'm using this code to count items on each worksheet in workbook 1. How do alter the code so that this part (ws.Range("C1").Value = cnt) is put into a different workbook? both workbooks have the same sheet names, if that helps.

Thanks!

Dim ws As Worksheet

With Sheet1
    Dim x As Long
   x = .Range("A" & Rows.Count).End(xlUp).Row
End With


For Each ws In Worksheets
  Dim cnt As Integer
  cnt = 0
  For i = 1 To x
    If ws.Cells(i, 1).Value = ws.Name And ws.Cells(i, 2).Value = "Football" Then
      cnt = cnt + 1
    End If
  Next i




ws.Range("C1").Value = cnt
Next

 

[MoshiM]

I'm using this code to count items on each worksheet in workbook 1. How do alter the code so that this part (ws.Range("C1").Value = cnt) is put into a different workbook? both workbooks have the same sheet names, if that helps.

Thanks!

Is

ws.Range("C1").Value = cnt

meant to be inside or outside the loop. As it is, you will just be overwriting the same cell each time the loop moves to another worksheet. Additionaly, is Sheet1 the actual name of the worksheet or are you referring to its codename.

Please read the comments

Dim ws As Worksheet, cnt As Long, x As Long, TempA As Variant

x = ThisWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
'assumes "Sheet1" is the name of the worksheet
For Each ws In ThisWorkbook.Worksheets
  
  With ws.UsedRange.Columns(1)
    TempA = .Resize(.Rows.Count, 2).Value2
  End With
  
  For I = 1 To x
    If TempA(I, 1) = ws.Name And TempA(I, 2) = "Football" Then
      cnt = cnt + 1
    End If
  Next I

Next
'assumes worksheet name in 2nd workbook is also "Sheet1" and that the cell is meant to be
'updated after exiting all loops

Workbooks("Name_of_Workbook_Here_with_extension").Worksheets("Sheet1").Range("C1").Value = cnt

 

[helpexcel]

Thanks. Sheet1 is the Codename. I did want that section in the loop, because there are multiple worksheets I want counted. For example i have sheets named NY, Philly, Dallas, Chicago, LA in both workbooks. I want to loop through each sheet in workbook1 and count, then i want it to loop through each corresponding sheet in workbook2 and put the result in C1.

 

[MoshiM]

Thanks. Sheet1 is the Codename. I did want that section in the loop, because there are multiple worksheets I want counted. For example i have sheets named NY, Philly, Dallas, Chicago, LA in both workbooks. I want to loop through each sheet in workbook1 and count, then i want it to loop through each corresponding sheet in workbook2 and put the result in C1.

Try this

Dim ws As Worksheet, cnt As Long, x As Long, TempA As Variant, Workbook_2 As Workbook


x = Sheet1.Range("A" & Rows.Count).End(xlUp).Row


Set Workbook_2 = Workbooks("Name_of_Workbook_Here_with_extension") 'workbook must be open


For Each ws In ThisWorkbook.Worksheets
  
    With ws.UsedRange.Columns(1)
        TempA = .Resize(.Rows.Count, 2).Value2
    End With
  
    With ws
  
        For i = 1 To x
            If TempA(i, 1) = .Name And TempA(i, 2) = "Football" Then
                cnt = cnt + 1
            End If
        Next i
  
        Workbook_2.Worksheets(.Name).Range("C1").Value = cnt


    End With
    
cnt = 0 'reset value of cnt


Next

 

[helpexcel]

I couldn't get the TempA code to run without getting an out of range error, so changed it back to .Cells and it ran perfectly. THANK YOU!!!!

 

[MoshiM]

I couldn't get the TempA code to run without getting an out of range error, so changed it back to .Cells and it ran perfectly. THANK YOU!!!!

If you want to use an array rather than checking each cell then change

[COLOR=#333333]TempA = .Resize(.Rows.Count, 2).Value2[/COLOR]

to

[COLOR=#333333]TempA = .Resize(x, 2).Value2[/COLOR]