Hello,

The following macro was build by me, with the recorder and help from
this site.

I have a problem when I change the workbook name into a higher
version. for example if the workbook is named TVA basic V6.06 and is
changed into V7.01 the macro don't work till I manually change the
name. Is there a method to overcome this problem ie that the search
value changes automatically when the workbook name is changed.

thanks in advance.



Sub Button4_Click()
Dim a As Integer
Dim c As Variant

Warn = "You are going to insert formulas in the OLAP extract. "
Warn = Warn & " Would like the formulas to be inserted. Continue?"
Ans = MsgBox(Warn, vbYesNo)
If Ans = vbYes Then
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"W:\Finance Divisional\OLAP\Reporting\Territory reporting\TVA
workfile\Factory_by_productcode.xls"
Sheets("factory").Select
Sheets("factory").Copy Befo=Workbooks("TVA basic V6.06.xls"). _
Sheets(34)
Sheets("OLAP extract").Select

a = 2
Range("ec" & a).Select
While Not IsEmpty(Range("ec" & a).Offset(0, -109).Value) '< ""
Range("ec" & a).Value = "=vlookup(v" & a & " ,markets!
R3c1:r66c3,3,false)"
Range("ed" & a).Value = "=+Assumptions!R1C4"
Range("ee" & a).Value = "=vlookup(s" & a & " ,factory!
R2c2:r5000c26,24,false)"
Range("DU" & a).Value = "=CN" & a & "-DY" & a
Range("DV" & a).Value = "=CO" & a & "-DZ" & a
Range("DW" & a).Value = "=CP" & a & "-EA" & a
a = a + 1

Wend
Cells.Select
Cells.EntireColumn.AutoFit
Application.ScreenUpdating = True
Columns("eC:ef").Select
Selection.Replace What:="'", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Columns("ee:ee").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("factory").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.delete
Application.DisplayAlerts = True
Sheets("OLAP extract").Select
Range("ec2").Select

End If

End Sub

Hi
Looks like your target workbook is already open so try this this

Sub Button4_Click()
Dim a As Integer
Dim c As Variant
Dim TargetWB as Workbook

Set TargetWB = Activeworkbook ' This is Workbooks("TVA basic
V6.06.xls"), assumed to be active
Warn = "You are going to insert formulas in the OLAP extract. "
Warn = Warn & " Would like the formulas to be inserted. Continue?"
Ans = MsgBox(Warn, vbYesNo)
If Ans = vbYes Then
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"W:\Finance Divisional\OLAP\Reporting\Territory reporting\TVA
workfile\Factory_by_productcode.xls"
Sheets("factory").Select
Sheets("factory").Copy Befo=TargetWB. _
Sheets(34)
Sheets("OLAP extract").Select
etc


regards
Paul


On Mar 6, 11:28 am, wrote:
Hello,

The following macro was build by me, with the recorder and help from
this site.

I have a problem when I change the workbook name into a higher
version. for example if the workbook is named TVA basic V6.06 and is
changed into V7.01 the macro don't work till I manually change the
name. Is there a method to overcome this problem ie that the search
value changes automatically when the workbook name is changed.

thanks in advance.

Sub Button4_Click()
Dim a As Integer
Dim c As Variant

Warn = "You are going to insert formulas in the OLAP extract. "
Warn = Warn & " Would like the formulas to be inserted. Continue?"
Ans = MsgBox(Warn, vbYesNo)
If Ans = vbYes Then
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"W:\Finance Divisional\OLAP\Reporting\Territory reporting\TVA
workfile\Factory_by_productcode.xls"
Sheets("factory").Select
Sheets("factory").Copy Befo=Workbooks("TVA basic V6.06.xls"). _
Sheets(34)
Sheets("OLAP extract").Select

a = 2
Range("ec" & a).Select
While Not IsEmpty(Range("ec" & a).Offset(0, -109).Value) '< ""
Range("ec" & a).Value = "=vlookup(v" & a & " ,markets!
R3c1:r66c3,3,false)"
Range("ed" & a).Value = "=+Assumptions!R1C4"
Range("ee" & a).Value = "=vlookup(s" & a & " ,factory!
R2c2:r5000c26,24,false)"
Range("DU" & a).Value = "=CN" & a & "-DY" & a
Range("DV" & a).Value = "=CO" & a & "-DZ" & a
Range("DW" & a).Value = "=CP" & a & "-EA" & a
a = a + 1

Wend
Cells.Select
Cells.EntireColumn.AutoFit
Application.ScreenUpdating = True
Columns("eC:ef").Select
Selection.Replace What:="'", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Columns("ee:ee").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("factory").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.delete
Application.DisplayAlerts = True
Sheets("OLAP extract").Select
Range("ec2").Select

End If

End Sub