Macro to Edit Workbook Links

[pkm1998]

Hi

I have written a macro which copies a formula to another worksheet ( paste special formulas), but the link to the sheet i am copying from is retained. I need to edit the link via a macro as i have 250 worksheets to go through.
I want to be to edit the link prior to moving to the next work book.
This is what i need to accomplish.. everything is working expect for being unable to edit links

[VBA]Sub CopyLookup()

Counter = 0
Do While Counter < 250
Counter = Counter + 1
If Counter = 250 Then
MsgBox "Last entry"
Else
Lookup 'this is where the data formula ( which is a vlookup) is being copied
' i would love to be able to edit the link just here.

ActiveWorkbook.Save
ActiveWorkbook.Close

End If
Loop
End Sub[/VBA]

Can anyone please help??

[acw]

Hi

What is the current formula, and what do you want to change it to? Perhaps attach a sheet showing what you want it to look like.


Tony

[pkm1998]

Hi Tony

This is what it looks like:

=-IF(ISNA(VLOOKUP('[ALH 4080 BalaclavaV2.xls]Input'!$B$2&'[ALH 4080 BalaclavaV2.xls]Input'!$A2&'[ALH 4080 BalaclavaV2.xls]Input'!$C2,'U:\[WOW F07 Retail Budget.xls]Data'!C$2:Q$7136!,'U:\[WOW F07 Retail
Budget.xls]Data'!$F$7140,FALSE)),0,VLOOKUP('[ALH 4080 BalaclavaV2.xls]Input'!$B$2&'[ALH 4080 BalaclavaV2.xls]Input'!$A2&'[ALH 4080 BalaclavaV2.xls]Input'!$C2,'U:\[WOW F07 Retail Budget.xls]Data'!C$2:Q$7136!,'U:\[WOW F07 Retail Budget.xls]Data'!$F$7140,FALSE))
I need to get rid of the ALH 4080 BalaclavaV2.xls so that it looks like this:

=-IF(ISNA(VLOOKUP(Input!$B$2&Input!$A1200&Input!$C1200,
'U:\[WOW F07 Retail Budget.xls]Data'!C$2:Q$7136,'U:\[WOW F07 Retail Budget.xls]Data'!$F$7140,FALSE)),0,VLOOKUP(Input!$B$2&Input!$A1200&Input!$C1200,'U:\[WOW F07 Retail Budget.xls]Data'!C$2:Q$7136,'U:\[WOW F07 Retail Budget.xls]Data'!$F$7140,FALSE))
Thanks

[acw]

Hi

Assuming that you are on the sheet that you want this replacement to occur then

[VBA]
ActiveSheet.Cells.Replace what:="[ALH 4080 BalaclavaV2.xls]", replacement:=""
[/VBA]

Tony

[pkm1998]

Thanks Tony,

I will give that a try and see if it works