VBA - how to add a copy and paste values on current module

[Minx_Minxy]

Hello all

I have the following VBA code

Sub Create()
    Dim application
    If Not IsObject(application) Then
       Set SapGuiAuto = GetObject("SAPGUI")
       Set application = SapGuiAuto.GetScriptingEngine
    End If
    If Not IsObject(Connection) Then
       Set Connection = application.Children(0)
    End If
    If Not IsObject(session) Then
       Set session = Connection.Children(0)
    End If
    If IsObject(WScript) Then
       WScript.ConnectObject session, "on"
       WScript.ConnectObject application, "on"
    End If
  
    Dim lastrow As Variant
    Dim i As Long
  
    lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
    
    On Error GoTo message
  
    For i = 2 To lastrow
        session.findById("wnd[0]/tbar[0]/okcd").Text = "/niw21"
        session.findById("wnd[0]").sendVKey 0
        session.findById("wnd[0]/usr/ctxtRIWO00-QMART").Text = "m7"
        session.findById("wnd[0]").sendVKey 0
        session.findById("wnd[0]/usr/subSCREEN_1:SAPLIQS0:1050/subNOTIF_TYPE:SAPLIQS0:1052/txtVIQMEL-QMTXT").Text = Range("a" & i).Value 'Text value
        session.findById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB01/ssubSUB_GROUP_10:SAPLIQS0:7235/subCUSTOM_SCREEN:SAPLIQS0:7212/subSUBSCREEN_1:SAPLIQS0:7322/subOBJEKT:SAPLIWO1:0100/ctxtRIWO1-TPLNR").Text = Range("b" & i).Value 'FuncLoc
        session.findById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB01/ssubSUB_GROUP_10:SAPLIQS0:7235/subCUSTOM_SCREEN:SAPLIQS0:7212/subSUBSCREEN_1:SAPLIQS0:7322/subOBJEKT:SAPLIWO1:0100/ctxtRIWO1-EQUNR").Text = Range("c" & i).Value 'Equipment
        session.findById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB01/ssubSUB_GROUP_10:SAPLIQS0:7235/subCUSTOM_SCREEN:SAPLIQS0:7212/subSUBSCREEN_4:SAPLIQS0:7715/cntlTEXT/shellcont/shell").Text = Range("d" & i).Value 'Description
        session.findById("wnd[0]/tbar[0]/btn[11]").press
        session.findById("wnd[1]/tbar[0]/btn[0]").press
        session.findById("wnd[0]/tbar[0]/okcd").Text = "/niw23"
        session.findById("wnd[0]").sendVKey 0
        Range("e" & i).Value = session.findById("wnd[0]/usr/ctxtRIWO00-QMNUM").Text 'Get Notification Number
    Next i

Exit Sub
message:
    MsgBox "There is an error, please check your SAP screen, revise the error, press enter whilst in SAP, then press OK below"
    Resume


End Sub

In Columns A, D and E, I have formulas. So when I run the code above, I get an error, because the cell is not in a value.

What I want the VBA to do then is, before going in SAP, I want excel to copy columns A, D and E and paste values only on the respective columns. I.E: Column A is pasted values only on Column A, Colum D values only pasted in Column D.

How do I do that?

I have tried adding the code at the beginning, before Dim, but I get an error.

 

[Artik]

Try modifying the code snippet this way:

(...)

    lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

    With Range("A2:A" & lastrow, "D2:E" & lastrow)
        .Value = .Value
    End With

    On Error GoTo message

(...)

Artik

 

[Minx_Minxy]

Try modifying the code snippet this way:

(...)

    lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

    With Range("A2:A" & lastrow, "D2:E" & lastrow)
        .Value = .Value
    End With

    On Error GoTo message

(...)

Artik

That worked a treat Artik! Thank you so much.