Anyone know what's wrong with this? Simply put its told to open two spreadsheets from a file location given 2 different cells. Then im trying to put the file location in a formula so it counts how many times the reference on the 2nd workbook, is on the 1st work book.
Every time i run it crashes when it gets to the COUNTIF formula part at the bottom and i can't work out where im going wrong. I haven't done macros in like 10 years. lol
For reference OldPath string is C:\Users\Nobbsy\Downloads\Copy of January 2024 Alterations.xlsx
Sub RRQP()
'RRQP Macro
'This macro will open a workbook
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Dim FullPath As String
Dim OldPath As String
FullPath = Range("G6")
OldPath = Range("G4")
Workbooks.Open (OldPath)
Workbooks.Open (FullPath)
ActiveSheet.Name = "Transaction Report"
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True
Range("D2").Select
' formula time
Dim last_row As Long
last_row = Cells(Rows.Count, 2).End(xlUp).Row
ActiveCell.Formula = "=COUNTIF(OldPath,Transaction Report'!$A$1:$A$10000,A2)"
ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":D" & last_row)
End Sub