Copy blocks of 2000 SAP work order numbers


This snippet is a general tool for copying blocks of work order numbers to the windows clipboard using Excel 2010 VBA for automation purposes when using SAP. This is a situation which can occur regularly when using SAP as some transactions have a limited capacity for processing multiple work orders

To use the code paste a column of work orders to column A. (possibly tens of thousands of work orders) Then in columns C and D enter the ranges of work orders that you want to copy and paste into SAP to run a transaction with 1000s of work orders. Click in column E against each range to copy them into the clipboard. Click in column G to return the focus to the top of the worksheet 


   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim sRange As String
    'Do nothing if more than one cell is selected
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column = 7 Then
        Application.CutCopyMode = False
        Range("A1").Select
    End If
    If Target.Column <> 5 Then Exit Sub ' only respond to column E
    If Range("C" & Target.Row).Text <> "" And Range("D" & Target.Row).Text <> "" And Range("F" & Target.Row).Text <> "OK" Then
        Application.CutCopyMode = False
        sRange = "F" & Target.Row
        Range(sRange).Select
        Selection.Value = "OK"
        ' for example - Range("A2:A501").Select
        sRange = "A" & Range("C" & Target.Row).Text & ":A" & Range("D" & Target.Row).Text
        Range(sRange).Select
        Selection.Copy
    End If
   End Sub


Tags - Microsoft Excel VBA, SAP, Excel 2010