Background: I'm trying dynamically to get some data from closed workbooks so I couldn't use Indirect or Index formula to do. So, I developed a code that insert the formula every time I create a worksheet. The code works fine apart from the issue explain below, so I haven't included the whole code, but just the bit I'm struggling.
I'm trying to insert the following formula into to a range like so:
Sub Create_WS_from_Overview()
Dim txt1 As String, txt2 As String, txt3 As String
Dim formtxt As String
txt1 = "=TRANSPOSE(FILTER("
txt2 = "'C://path/[Part_of_Workbook_Name_"
txt3 = ".xlsx]WorksheetName'!$C$3:$W$3"
formtxt = txt1 & txt2 & ActiveSheet.Name & txt3 & ", " & txt2 & ActiveSheet.Name & txt3 & "<>0))"
ActiveSheet.Range("A2").Formula = formtxt
End Sub
The code works fine but it's inserting @ in the formula so it doesn't give the range I want.
Do you have any ideas why it does that and how to fix it? (If I manually remove it the @ works fine)
Cheers
