Recorded macro's to be simplified??

[A. Karatas]

Hello,

I have three macro's who have initially been recorded, but with the
help of this forum altered manually a little bit. Since this morning
the damn thing doesn't work for a reason I do not know. My question to
you guys is you can have a look at the macro's and come with solutions
to the problem or give me a revised and more reliable macro. Al the
ranges that are selected in the macro are as shown.


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


Set TargetWB = ActiveWorkbook
warn = "You are going to insert formulas for the YEAR END reporting in
the OLAP extract. 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

a = 2
Range("ec" & a).Select
While Not IsEmpty(Range("ec" & a).Offset(0, -132).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("ef" & a).Value = "=if(bg" & a & "+bi" & a & "+z" & a &
"+ab" & a & "+du" & a & "+dw" & a & "=0,0,1)"
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
Range("EC2").Select
Range(selection, selection.End(xlToRight)).Select
Range(selection, selection.End(xlDown)).Select
selection.Copy
Application.CutCopyMode = False
selection.Replace What:="'", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
selection.Copy
selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("factory").Select
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.delete
Application.DisplayAlerts = True
Sheets("Checklist").Select
Range("A1").Select
Application.ScreenUpdating = True
End If

End Sub

A detail to this macro is that the vlookup function gives the ' as a
value in the formula. That's why I recorded the find and replace
function. Maybe I entered the vlookup function wrong!! The macro gives
an compile error (expected function or variable) after WEND for the
selection.

the other macro is
Sub delete1()
'
' delete Macro
' Macro recorded 27-2-2007 by Karata01
'

Application.ScreenUpdating = False
Range("A1").Select
Sheets("P&L current").Select
Range("E13:BE58").Select
selection.ClearContents
Sheets("P&L last").Select
Range("E13:BE58").Select
selection.ClearContents
Sheets("Hyperion P&L").Select
Range("F9:J40").Select
selection.ClearContents
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("OLAP extract").Select
ActiveWindow.SmallScroll Down:=-60
Range("A2:Ex60000").Select
selection.ClearContents
Sheets("Checklist YTD").Select
Range("A1").Select
Application.ScreenUpdating = True
End Sub

and

Sub Button3_Click()
'
' Button3_Click Macro
' Macro recorded 27-2-2007 by Karata01
'


Dim TargetWB As Workbook


Set TargetWB = ActiveWorkbook
warn = "You are going to insert P&L data. "
warn = warn & " Would like the data to be inserted. "
Ans = MsgBox(warn, vbYesNo)
If Ans = vbYes Then
Application.ScreenUpdating = False
Workbooks.Open FileName:="W:\Finance Divisional\OLAP\Reporting
\Territory reporting\TVA workfile\p&l basic.xls"
Windows("P&l basic.xls").Activate
Sheets("Cost detail Current Period").Select
Range("A1:be62").Select
selection.Copy
TargetWB.Activate
Sheets("P&L current").Select
Range("A3").Select
selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("P&L basic.xls").Activate
Sheets("cost detail last period").Select
Range("A1:BE62").Select
Application.CutCopyMode = False
selection.Copy
TargetWB.Activate
Sheets("P&L last").Select
Range("A3").Select
selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Windows("P&L basic.xls").Activate
Sheets("P&L").Select
Range("A1:J30").Select
Application.CutCopyMode = False
selection.Copy
TargetWB.Activate
Sheets("Hyperion P&L").Select
Range("A4").Select
selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("Checklist YTD").Select
Range("A1").Select
Application.ScreenUpdating = True
End If

End Sub

 

[A. Karatas]

I found the problem for the error. I had recorded a macro which was
named selection (stupid I know). But still could you guys have a look
at the macro's and inform me about modifications if necessary.

thankx