0

I am pretty new to VBA and was trying to automate a task of copying, pasting and applying a few formulae using VBA. While I could automate the task and it works perfectly in my system how much ever times I run it, it only runs once in my colleague's system and breaks the second time with the error: VBA Runtime Error 1004 "Application-defined or Object-defined error". Following is where the code breaks:

''Clear earlier data
WB.Sheets("Sales by customer sheet").Activate
Range("A3:XFD1048576").Cells.Select
Selection.ClearContents

''YTD Delivered
Range("B3").Select
ActiveCell.Formula2R1C1 = "=UNIQUE(Sales_by_Customer_Table[name])"  ''' This is where the code breaks with given error
Range(Selection, Selection.END(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("C3").Select
ActiveCell.FormulaR1C1 = _
    "=SUMIFS(Sales_by_Customer_Table[delivery_amount],Sales_by_Customer_Table[delivery_date],"">=""&DATE(YEAR(TODAY()),1,1),Sales_by_Customer_Table[delivery_date],""<=""&TODAY(),Sales_by_Customer_Table[name],'Sales by customer sheet'!RC[-1])"
Selection.Copy
Range("B3").Select
Selection.END(xlDown).Offset(0, 1).Select
Range(Selection, Selection.END(xlUp)).Select
ActiveSheet.Paste

I am not sure what is the issue since it only creates an issue in my colleague's system.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Kuljeet Keshav
  • 125
  • 1
  • 4
  • 5
    Please see: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba Avoiding `.Select` and `.Activate` will fix your problem. – Scott Craner May 04 '23 at 17:04
  • 3
    What error do you get when you enter that formula manually? My guess is that table and/or table column doesn't exist. – BigBen May 04 '23 at 17:05

0 Answers0