Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded macro's to be simplified??
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 Befo=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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recorded macro's to be simplified??
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
looking for simplified formulas | Excel Worksheet Functions | |||
Hide Macro's in Toolbar / Macro's list | Excel Discussion (Misc queries) | |||
Can this be simplified | Excel Worksheet Functions | |||
Can this be simplified? | Excel Programming | |||
Simplified Help | Excel Programming |