First, delete the code you currently have in Module1. Then in Sheet1, paste the following code.
Option Explicit
Dim rng As Range
Private Sub CopyPhase()
With ComboBox2
If .Value <> "" Then
Set rng = Sheet2.Rows(6).Find(What:=.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
Range("B13:H39").Copy Destination:=rng.Offset(1, 0)
End If
End If
End With
End Sub
Private Sub RecallPhase()
With ComboBox2
If .Value <> "" Then
Set rng = Sheets("Sheet2").Rows(6).Find(What:=.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
With Sheets("Sheet2")
.Range(.Cells(rng.Row + 2, rng.Column), .Cells(rng.Row + 27, rng.Column + 6)).Copy Destination:=Range("B14")
End With
End If
End If
End With
End Sub
Assign your Recall Invoice button to the RecallPhase macro, and the Memorize button to the CopyPhase macro.
You'll also need to deal with another problem. Sheet2 has a ComboBox1_Change event behind it, which apparently gets triggered every time a value is changed anywhere in the workbook (even on another sheet). So when the CopyPhase or RecallPhase macros hit the lines that copy the data, it triggers the event, which blows up because Sheet2 isn't the current sheet. This apparently occurs because of your dynamic range (Client1) ListFillRange for your ComboBoxes. Any value change causes the dynamic range to be recalculated, which reloads the combobox, triggering the event. I learned of this from the following: http://www.mrexcel.com/forum/excel-q...worksheet.html. Perhaps you can fix it by changing OFFSET to INDEX as was suggested, or by making it a normal named range instead of dynamic. If you'd rather not, you can update the Change event to ignore the error with the following code:
On Error Resume Next
Application.Goto ActiveSheet.Rows(6).Find(What:=.Value, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, -1), Scroll:=True
I hope that gets you what you need.
Bookmarks