Macro help

[Visakh]

Hi,

I have recorded the below macro. However it is taking loads of time to run. Is there a way to ensure macro will delete old data on the file (with click of a button) and let the user start a fresh. Same time it won't take time to run.

The vlookup should be done only if columns C as value.

Sub Button1_Click()
'
' Button1_Click Macro
'

'
Columns("AB:AB").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("AD2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-27],Sheet3!C[-28]:C[-26],3,0)"
Range("AD2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("AE2").Select
ActiveCell.FormulaR1C1 = "=RC[-24]+RC[-1]"
Range("AE2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("AF2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*30%"
Range("AF2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
ActiveWindow.ScrollRow = 1047583
ActiveWindow.ScrollRow = 1021370
ActiveWindow.ScrollRow = 930561
ActiveWindow.ScrollRow = 806049
ActiveWindow.ScrollRow = 681538
ActiveWindow.ScrollRow = 561707
ActiveWindow.ScrollRow = 496175
ActiveWindow.ScrollRow = 464345
ActiveWindow.ScrollRow = 425025
ActiveWindow.ScrollRow = 398812
ActiveWindow.ScrollRow = 364174
ActiveWindow.ScrollRow = 349195
ActiveWindow.ScrollRow = 328599
ActiveWindow.ScrollRow = 315493
ActiveWindow.ScrollRow = 298641
ActiveWindow.ScrollRow = 288343
ActiveWindow.ScrollRow = 275237
ActiveWindow.ScrollRow = 264939
ActiveWindow.ScrollRow = 248088
ActiveWindow.ScrollRow = 235918
ActiveWindow.ScrollRow = 225620
ActiveWindow.ScrollRow = 213449
ActiveWindow.ScrollRow = 202215
ActiveWindow.ScrollRow = 186300
ActiveWindow.ScrollRow = 169449
ActiveWindow.ScrollRow = 154470
ActiveWindow.ScrollRow = 138555
ActiveWindow.ScrollRow = 124513
ActiveWindow.ScrollRow = 111406
ActiveWindow.ScrollRow = 97363
ActiveWindow.ScrollRow = 79576
ActiveWindow.ScrollRow = 71150
ActiveWindow.ScrollRow = 65533
ActiveWindow.ScrollRow = 60852
ActiveWindow.ScrollRow = 58980
ActiveWindow.ScrollRow = 55235
ActiveWindow.ScrollRow = 52427
ActiveWindow.ScrollRow = 50555
ActiveWindow.ScrollRow = 47746
ActiveWindow.ScrollRow = 44001
ActiveWindow.ScrollRow = 37448
ActiveWindow.ScrollRow = 31831
ActiveWindow.ScrollRow = 21533
ActiveWindow.ScrollRow = 8427
ActiveWindow.ScrollRow = 1
Range("AG2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("AG2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
ActiveWindow.ScrollRow = 1046647
ActiveWindow.ScrollRow = 1007327
ActiveWindow.ScrollRow = 960518
ActiveWindow.ScrollRow = 900603
ActiveWindow.ScrollRow = 835071
ActiveWindow.ScrollRow = 793879
ActiveWindow.ScrollRow = 734900
ActiveWindow.ScrollRow = 641282
ActiveWindow.ScrollRow = 540175
ActiveWindow.ScrollRow = 438132
ActiveWindow.ScrollRow = 352003
ActiveWindow.ScrollRow = 286471
ActiveWindow.ScrollRow = 249024
ActiveWindow.ScrollRow = 205024
ActiveWindow.ScrollRow = 145108
ActiveWindow.ScrollRow = 101108
ActiveWindow.ScrollRow = 64597
ActiveWindow.ScrollRow = 34640
ActiveWindow.ScrollRow = 12171
ActiveWindow.ScrollRow = 1
Range("AH2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-3]"
Range("AH2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("A1").Select
End Sub

 

[mohadin]

Hi
Try

Sub Button1_Click()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
[AB:AB].Cut
[C:C].Insert Shift:=xlToRight
lr = Cells(Rows.Count, "AD").End(xlUp).Row - 1
Range("AD2").Resize(lr).FormulaR1C1 = "=VLOOKUP(RC[-27],Sheet3!C[-28]:C[-26],3,0)"
Range("AE2").Resize(lr).FormulaR1C1 = "=RC[-24]+RC[-1]"
Range("AF2").Resize(lr).FormulaR1C1 = "=RC[-1]*30%"
Range("AG2").Resize(lr).FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("AH2").Resize(lr).FormulaR1C1 = "=RC[-1]-RC[-3]"
Range("A1").Select
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[Visakh]

Hi
Try

Sub Button1_Click()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
[AB:AB].Cut
[C:C].Insert Shift:=xlToRight
lr = Cells(Rows.Count, "AD").End(xlUp).Row - 1
Range("AD2").Resize(lr).FormulaR1C1 = "=VLOOKUP(RC[-27],Sheet3!C[-28]:C[-26],3,0)"
Range("AE2").Resize(lr).FormulaR1C1 = "=RC[-24]+RC[-1]"
Range("AF2").Resize(lr).FormulaR1C1 = "=RC[-1]*30%"
Range("AG2").Resize(lr).FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("AH2").Resize(lr).FormulaR1C1 = "=RC[-1]-RC[-3]"
Range("A1").Select
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Thanks for looking into it.
It is working, however is there a way in which we can make the vlookup formula be dragged down till whereever column C as a value?

 

[mohadin]

Sub Button1_Click()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
[AB:AB].Cut
[C:C].Insert Shift:=xlToRight
lr = Cells(Rows.Count, "AD").End(xlUp).Row - 1
Range("AD2").Resize(lr).FormulaR1C1 = "=VLOOKUP(RC[-27],Sheet3!C[-28]:C[-26],3,0)"
Range("AE2").Resize(lr).FormulaR1C1 = "=RC[-24]+RC[-1]"
Range("AF2").Resize(lr).FormulaR1C1 = "=RC[-1]*30%"
Range("AG2").Resize(lr).FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("AH2").Resize(lr).FormulaR1C1 = "=RC[-1]-RC[-3]"
Range("A1").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
[B][COLOR=#000000]With Range("AD2").Resize(lr)[/COLOR][/B]
[B][COLOR=#000000].Value = .Value[/COLOR][/B]
[B][COLOR=#000000]End With[/COLOR][/B]
End Sub