title: SAP software to Excel - Macro doesn't paste the values in a spreadsheet
body: <p>can someone check the code below and tell if I'm missing something or there is another way to make this work?</p>
  <p>To sumup: I've created a Macro that connects with SAP Software, starts a Loop getting data from a table, pastes in excel and goes to the other account until the loop ends.</p>
  <p>I've extracted a script from SAP software to get the values from a table displayed at the end of MB5L transaction.</p>
  <p>the code at the end loops to get data from accounts, paste the values in a tab named &quot;temp&quot;, formats it, copy the data in a tab named base a goes to another account.</p>
  <p>In the below block the command <code>Application.SendKeys &quot;^v&quot;, True</code> the data is not pasted:</p>
  <pre><code>        thisworkbook.Sheets(&quot;temp&quot;).Activate
          Application.Wait (Now() + TimeValue(&quot;00:00:03&quot;))
          Range(&quot;A1&quot;).Select
          Application.Wait (Now() + TimeValue(&quot;00:00:03&quot;))
          Application.SendKeys &quot;^v&quot;, True
          Application.Wait (Now() + TimeValue(&quot;00:00:03&quot;))
  </code></pre>
  <p>If instead of this block I use the command <code>ThisWorkbook.ActiveSheet.Paste</code>, the data comes wrong.</p>
  <p>Full code below:</p>
  <pre><code>Sub MB5L()
      Dim appl, SapGuiAuto, Connection, session, WScript
      Dim i As Integer
  
      Set rawben = Sheets(&quot;Base&quot;)
      lastRow = rawben.Cells(Rows.Count, &quot;AK&quot;).End(xlUp).Row
         
      thisworkbook.Sheets(&quot;base&quot;).Select
  
  
  ' this block checks connection with SAP software
      If Not IsObject(appl) Then
         Set SapGuiAuto = GetObject(&quot;SAPGUI&quot;)
         Set appl = SapGuiAuto.GetScriptingEngine
      End If
      If Not IsObject(Connection) Then
         Set Connection = appl.Children(0)
      End If
      If Not IsObject(session) Then
         Set session = Connection.Children(0)
      End If
      If IsObject(WScript) Then
         WScript.ConnectObject session, &quot;on&quot;
         WScript.ConnectObject appl, &quot;on&quot;
      End If
      
      
              On Error Resume Next
              For i = 1 To 30
                  session.findById(&quot;wnd[0]&quot;).sendVKey 3 'press f3 until it gets in homepage
                  session.findById(&quot;wnd[0]&quot;).sendVKey 12 'press f12 until it gets in homepage
                  session.findById(&quot;wnd[1]&quot;).Close
              Next i
              On Error GoTo 0
      
      session.findById(&quot;wnd[0]&quot;).maximize
      On Error Resume Next
      For i = 2 To lastRow 'loop to get the account number
          If Not rawben.Range(&quot;AK&quot; &amp; i).Value &lt;&gt; &quot;&quot; Then Exit For
  
          session.findById(&quot;wnd[0]/tbar[0]/okcd&quot;).Text = &quot;MB5L&quot; ' access transaction
          session.findById(&quot;wnd[0]/tbar[0]/btn[0]&quot;).press
          session.findById(&quot;wnd[0]/usr/radVMSALDO&quot;).Select
          session.findById(&quot;wnd[0]/usr/ctxtBUKRS-LOW&quot;).Text = &quot;0001&quot; 'company code
          session.findById(&quot;wnd[0]/usr/ctxtSKONT-LOW&quot;).Text = rawben.Range(&quot;AK&quot; &amp; i).Value 'types the account
          session.findById(&quot;wnd[0]/usr/radVMSALDO&quot;).SetFocus
          session.findById(&quot;wnd[0]/tbar[1]/btn[8]&quot;).press
          session.findById(&quot;wnd[0]/usr/lbl[12,5]&quot;).SetFocus
          session.findById(&quot;wnd[0]/usr/lbl[12,5]&quot;).caretPosition = 9
          session.findById(&quot;wnd[0]&quot;).sendVKey 2
          session.findById(&quot;wnd[0]/mbar/menu[0]/menu[1]/menu[2]&quot;).Select 'selects saving mode and pops up a window
          session.findById(&quot;wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]&quot;).Select 'selects the &quot;save in clipboard option
          session.findById(&quot;wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]&quot;).SetFocus
          session.findById(&quot;wnd[1]/tbar[0]/btn[0]&quot;).press 'hits the button to save in clipboard
          session.findById(&quot;wnd[0]&quot;).sendVKey 3 'press f3 to go back 1 page
          session.findById(&quot;wnd[0]&quot;).sendVKey 3 'press f3 to go back 1 page
          session.findById(&quot;wnd[0]&quot;).sendVKey 3 'press f3 to go back 1 page
              
  
          thisworkbook.Application.WindowState = xlMaximized
          thisworkbook.Sheets.Add.Name = &quot;temp&quot; 'adds a new tab to paste and format values
          Application.Wait (Now() + TimeValue(&quot;00:00:01&quot;))
          thisworkbook.Sheets(&quot;temp&quot;).Activate
          Application.Wait (Now() + TimeValue(&quot;00:00:03&quot;))
          Range(&quot;A1&quot;).Select
          Application.Wait (Now() + TimeValue(&quot;00:00:03&quot;))
          Application.SendKeys &quot;^v&quot;, True
          Application.Wait (Now() + TimeValue(&quot;00:00:03&quot;))
                                 
  
          If Range(&quot;b2&quot;).Characters.Count = 0 Then 'checks the content of cell B2, if it matches the criteria the next step will be a &quot;text to columns&quot;
                  Columns(&quot;A:A&quot;).Select
                  Selection.TextToColumns Destination:=Range(&quot;A1&quot;), DataType:=xlDelimited, _
                      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                      Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
                      :=&quot;|&quot;, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
                      1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
                      , 1), Array(13, 1), Array(14, 1)), TrailingMinusNumbers:=True
                  Selection.Delete Shift:=xlToLeft
                  Rows(&quot;1:1&quot;).Delete
                  Rows(&quot;2:2&quot;).Delete
                  Columns(&quot;A:A&quot;).Delete
              
          Else 'if the previous criteria is no true then the next step is to delete rows and columns
                      
                  Rows(&quot;1:1&quot;).Delete
                  Rows(&quot;2:2&quot;).Delete
                  Columns(&quot;A:A&quot;).Delete
              
          End If
                      
          Dim nextLine: nextLine = thisworkbook.Sheets(&quot;base&quot;).Cells(Rows.Count, &quot;A&quot;).End(xlUp).Row + 1 'this block is to get the values and move to another tab _
      right below the previous line with values
                      
          Sheets(&quot;temp&quot;).Select
          Rows(&quot;2:2&quot;).Select
          Range(Selection, Selection.End(xlDown)).Select
          Selection.Copy
                      
          thisworkbook.Sheets(&quot;base&quot;).Range(&quot;A&quot; &amp; nextLine).PasteSpecial Paste:=xlPasteValues
          thisworkbook.Sheets(&quot;base&quot;).Range(&quot;A&quot; &amp; nextLine).PasteSpecial Paste:=xlPasteFormats
                      
          Application.CutCopyMode = False
                      
          Application.DisplayAlerts = False
          thisworkbook.Sheets(&quot;temp&quot;).Delete
  
      Next i
      On Error GoTo 0
  
  'Message at the ending of the extraction
      Application.ScreenUpdating = True
      MsgBox &quot;Extraction complete&quot;, vbInformation
  
  End Sub
  </code></pre>