Hi - I have workbookt1 that has all of my data in it, and workbook2 that i want to use a tracker. The code i'm using is below. Is there a way to use the sheet name a a variable in place of "Apple" and "Pear"? Both workbooks have the same sheet names and ideally the code would put the totals on the corresponding sheet in workbook 2. I'm also having an issue with my nextrow language...it's not going to the next row, it's using the same row each time the code is run.
Thanks!
Thanks!
Code:
Dim WB As Workbook
On Error Resume Next
Set WB = Workbooks("Workbook2.xlsm")
If Err <> 0 Then
On Error GoTo 0
Workbooks.Open ("Workbook2.xlsm")
End If
Dim wbT As Workbook
Dim wsT As Worksheet
Set wbT = Workbooks("Workbook2.xlsm")
Set wsT = wbT.Worksheets("Sheet1")
With wsT
NxtRw = .Cells(Rows.Count, 1).End(xlUp).Row + 1
End With
With Sheet1
Dim x As Long
x = .Range("A" & Rows.Count).End(xlUp).Row
If x < 1 Then x = 1
wsT.Range("B" & NxtRw) = Application.WorksheetFunction.CountIf(.Range("J2:J" & x), "Apple") + Application.WorksheetFunction.CountIf(.Range("J2:J" & x), "Pear")
End With
With Sheet23
Dim cnt As Integer
cnt = 0
For i = 1 To x
If .Cells(i, 14).Value = "Apple" And .Cells(i, 13).Value = "Green" Then
cnt = cnt + 1
End If
Next i
End With
wsT.Range("E" & NxtRw).Value = cnt
With Sheet23
Dim scr As Integer
scr = 0
For j = 1 To x
If .Cells(j, 14).Value = "Apple" And .Cells(j, 3).Value <= "50" Then
scr = scr + 1
End If
Next j
End With
wsT.Range("C" & NxtRw).Value = scr