PDA

View Full Version : Macro to Edit Workbook Links


pkm1998
06-14-2006, 08:53 PM
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

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

Can anyone please help??:banghead:
acw
06-14-2006, 09:53 PM
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
06-14-2006, 10:00 PM
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
06-18-2006, 09:49 PM
Hi

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


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


Tony
pkm1998
06-18-2006, 09:51 PM
Thanks Tony,

I will give that a try and see if it works