Excel to VBA code

[Krishna2385]

Hi Team,

I Have an Excel Macro, however when I checked with the VBA macro it is too huge.

Can the below macro be converted to VBA macro or I need to write a new one

Sub Macro2()
'
' Macro2 Macro
'

'
  ActiveCell.FormulaR1C1 = _
  "=VLOOKUP(RC[-1],'[PeopleSoft SPR query.xlsx]Sheet1'!C1:C2,2,0)"
  Range("F2").Select
  Selection.Copy
  Range("E2").Select
  Selection.End(xlDown).Select
  Range("F137").Select
  Range(Selection, Selection.End(xlUp)).Select
  Range("F3:F137").Select
  Range("F137").Activate
  ActiveSheet.Paste
  Application.CutCopyMode = False
  Range("F136").Select
  Selection.End(xlUp).Select
  Range("G2").Select
  ActiveCell.FormulaR1C1 = _
  "=VLOOKUP(RC[-2],'[PeopleSoft SPR query.xlsx]Sheet1'!C1:C4,4,0)"
  Range("G2").Select
  Selection.Copy
  Range("F2").Select
  Selection.End(xlDown).Select
  Range("G137").Select
  Range(Selection, Selection.End(xlUp)).Select
  Range("G3:G137").Select
  Range("G137").Activate
  ActiveSheet.Paste
  Application.CutCopyMode = False
  Selection.End(xlUp).Select
  Range("H2").Select
  ActiveCell.FormulaR1C1 = _
  "=VLOOKUP(RC[-3],'[PeopleSoft SPR query.xlsx]Sheet1'!C1:C5,5,0)"
  Range("H2").Select
  Selection.Copy
  Range("G2").Select
  Selection.End(xlDown).Select
  Range("H137").Select
  Range(Selection, Selection.End(xlUp)).Select
  Range("H3:H137").Select
  Range("H137").Activate
  ActiveSheet.Paste
  Application.CutCopyMode = False
  Selection.End(xlUp).Select
  Range("I2").Select
  ActiveCell.FormulaR1C1 = _
  "=VLOOKUP(RC[-4],'[PeopleSoft SPR query.xlsx]Sheet1'!C1:C3,3,0)"
  Range("I2").Select
  Selection.Copy
  Range("H2").Select
  Selection.End(xlDown).Select
  Range("I137").Select
  Range(Selection, Selection.End(xlUp)).Select
  Range("I3:I137").Select
  Range("I137").Activate
  ActiveSheet.Paste
  Application.CutCopyMode = False
  Selection.End(xlUp).Select
  Range("F2:I2").Select
  Range(Selection, Selection.End(xlDown)).Select
  Selection.Copy
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
  Range("F1").Select
  Application.CutCopyMode = False
End Sub

Please help in getting this sorted out.
Many thanks

 

[Marc L]

Hi !

Please edit your post and use code tags or the specific code icon, thanks !

As it's not a macro (as macros disappears at least 20 years ago !)
but already a VBA code so nothing to do …
​

 

[Krishna2385]

Sorry , how to edit I dint find any option.

And isn't noting to do with this code... thought this can be shirked.
thanks

 

[Luke M]

Here's your code, simplified down at least.

Sub CleanedCode()
   
    Application.ScreenUpdating = False
   
    'Dangerous, don't know what active cell will be!
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[PeopleSoft SPR query.xlsx]Sheet1'!C1:C2,2,0)"
    Range("F2").Copy Range("F3:F137")
    Range("G2").FormulaR1C1 = _
        "=VLOOKUP(RC[-2],'[PeopleSoft SPR query.xlsx]Sheet1'!C1:C4,4,0)"
    Range("G2").Copy Range("G3:G137")
    Range("H2").FormulaR1C1 = _
        "=VLOOKUP(RC[-3],'[PeopleSoft SPR query.xlsx]Sheet1'!C1:C5,5,0)"
    Range("H2").Copy Range("H3:H137")
    Range("I2").FormulaR1C1 = _
        "=VLOOKUP(RC[-4],'[PeopleSoft SPR query.xlsx]Sheet1'!C1:C3,3,0)"
    Range("I2").Copy Range("I3:I137")
    With Range("F2:I137")
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
   
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[Krishna2385]

Thanks a lot... wish I could buy u a drink