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 Before:=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 Before:=TargetWB. _
Sheets(34)
Sheets("OLAP extract").Select
etc


regards
Paul


On Mar 6, 11:28 am, a.kara...@gmail.com 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 Before:=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