0

I have a table of data in a sheet which I am using to copy a number of select rows in to another workbook. Once I have the rows I convert the data in the destination sheet to a table and give it the same table name as the source, I do the table conversion with -

Dim srcRangeForTbl As Range
Set srcRangeForTbl = wsDestinationSheet.Range("A1").CurrentRegion
wsDestinationSheet.ListObjects.Add(SourceType:=xlSrcRange, Source:=srcRangeForTbl).Name = "AllStaffProjectAllocationTbl" 
wsDestinationSheet.ListObjects("AllStaffProjectAllocationTbl").TableStyle = ""

I have the following formula in W2 in the source sheet that then graphs that data from the source table in to a gannt chart.

=IF((AllStaffProjectAllocationTbl[Start Date]<DATE(YEAR(W1:YE1),MONTH(W1:YE1)+1,1))*(AllStaffProjectAllocationTbl[End Date]>=W1:YE1),AllStaffProjectAllocationTbl[Employee],"")

I copy the formula in to the destination sheet with the following code -

SheetSource.Range("W2").Copy
wsDestinationSheet.Range("W2").PasteSpecial Paste:=xlPasteFormulas

Unfortunately this takes the above formula from W2 and adds references to the original workbook as below -

=IF((Project.xlsm!AllStaffProjectAllocationTbl[Start Date]<DATE(YEAR(W1:YE1),MONTH(W1:YE1)+1,1))*(Project.xlsm!AllStaffProjectAllocationTbl[End Date]>=W1:YE1),Project.xlsm!AllStaffProjectAllocationTbl[Employee],"")

Is there a way to exclude these workbook references?

Automation Monkey
  • 355
  • 1
  • 8
  • `wsDestinationSheet.Range("W2").Formula=SheetSource.Range("W2").Formula` ? – Tim Williams Oct 19 '22 at 16:46
  • FYI the `ListObjects.Add` method returns the added listobject so you could do something like `With wsDestinationSheet.ListObjects.Add(SourceType:=xlSrcRange, Source:=srcRangeForTbl): .Name = "AllStaffProjectAllocationTbl": .TableStyle = "": End With` – Tim Williams Oct 19 '22 at 16:56
  • Hi @TimWilliams unfortunately I'm getting application defined or object defined error when trying your suggested solution. Any idea why I'd be getting this? – Automation Monkey Oct 19 '22 at 17:27
  • If you copy the formula (not the cell with the formula) from the source cell and try to paste it in the formula bar for the destination cell, does it give any error? Are the two sheets in different workbooks? – Tim Williams Oct 19 '22 at 17:42
  • I previously tried copying (Ctrl+c) the raw formula in to the cell in the destination sheet to check it’d work prior to working on the vba copy code and it does. The sheets are in different workbooks. – Automation Monkey Oct 19 '22 at 19:03
  • If that works then I can't say why assigning the formula directly wouldn't work. – Tim Williams Oct 19 '22 at 20:04
  • Hey @TimWilliams I got it working. The issue was the code for converting to a table needed to come before the function copy - which makes sense since the formula references a table that didn't exist at that point. However, the resulting function has a bunch of @ symbols in it stopping it from working - `=@IF((AllStaffProjectAllocationTbl[@[Start Date]]=@W1:YE1),AllStaffProjectAllocationTbl[Employee],"")` If I remove the @ symbols the function works fine. Do you know how I stop these copying over? – Automation Monkey Oct 20 '22 at 12:50
  • See: https://stackoverflow.com/questions/61138029/excel-vba-how-to-add-dynamic-array-formula – Tim Williams Oct 20 '22 at 16:06

1 Answers1

0

As suggested by Tim Williams -

wsDestinationSheet.Range("W2").Formula2=SheetSource.Range("W2").Formula2

Automation Monkey
  • 355
  • 1
  • 8