nigelandrewfoster
Well-known Member
- Joined
- May 27, 2009
- Messages
- 747
Hello there - strange one this. If I run the code below using a button on a userform it executes perfectly. When I attempt to fire it using a keyboard shortcut it stops executing after the Workbook.Open line (highlighted). Any idea why? And how I can solve this?
Many thanks
Many thanks
VBA Code:
Sub Edit_Orders()
Dim wbOrders As Workbook
Dim rngLastRow As Range
Dim avarOrders() As Variant
Dim strNotes As String
Dim lngGatherRow As Long
Dim lngDataRows As Long
Dim i As Long
Dim j As Long
[B]Set wbOrders = OpenWorkbook(MyDocumentsPath & "+MAIN WORKBOOKS+\", "+Orders+.xlsx")[/B]
If Not wbOrders Is Nothing Then
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Clipboard")
avarOrders = .Range(.[b2], .[b1048576].End(xlUp)).Offset(, -1).Resize(, 25).Value
End With
i = 1
lngDataRows = UBound(avarOrders)
Do While i <= lngDataRows
If IsEmpty(avarOrders(i, 13)) Then
j = 1
lngGatherRow = i - 1
strNotes = avarOrders(lngGatherRow, 16)
Do While IsEmpty(avarOrders(i, 13))
For j = 1 To 24
If Not IsEmpty(avarOrders(i, j)) Then
If Not IsError(avarOrders(i, j)) Then strNotes = strNotes & " " & avarOrders(i, j) ' Gather split notes together
avarOrders(i, j) = Empty
End If
Next
i = i + 1
Loop
avarOrders(lngGatherRow, 16) = strNotes
End If
If strNotes <> "" Then
strNotes = ""
Else
i = i + 1
End If
Loop
With wbOrders.Worksheets("Orders")
.Cells.ClearContents
.[a2].Resize((lngDataRows), 25).Value = avarOrders
ThisWorkbook.Worksheets("Clipboard").[a1:y1].Copy .[a1]
.[a1].EntireColumn.Delete Shift:=xlLeft
.[a1:x1].Resize(lngDataRows + 1).Sort Key1:=.[b1], Order1:=xlAscending, Header:=xlYes
Set rngLastRow = .[a1048576].End(xlUp).EntireRow
rngLastRow.Resize(1048576 - rngLastRow.Row).Offset(1).Delete
End With
Application.ScreenUpdating = True
End If
End Sub