martinargimon
Member
HI There.
I have copied my code underneath, and i wonder if you could please assist
The program is about clients buying a 'Funeral policy', which includes a 'grave' in the Memorial Park.
The Sheets "Orders" ( shOrders), holds each order number , whereas the sheet "Beneficiaries_Burials" ( sh) holds how many beneficiaries are covered under each policy in this particular order ( policy).
The final objective of the code is to print in the sheet "R_Purchase_Summary", all the details of the Order ( including all beneficiaries).
The problem that I have is, that sometimes the order itself has NOT yet been allocated beneficiaries names, and hence there are NO beneficiaries to be extracted when querying the "sh" sheet ( see Autofilter method , within the first With statement). When there are NO beneficiaries it prints the whole filtered list instead of "nothing" (blank) , as it should be.
I've tried the 'On error" ( but is NOT really an error)..... I also tried Specialcells , but i am not quite sure to make it work.
Hope i was clear with my explanation , otherwise please let me know. Many Thanks
I have copied my code underneath, and i wonder if you could please assist
The program is about clients buying a 'Funeral policy', which includes a 'grave' in the Memorial Park.
The Sheets "Orders" ( shOrders), holds each order number , whereas the sheet "Beneficiaries_Burials" ( sh) holds how many beneficiaries are covered under each policy in this particular order ( policy).
The final objective of the code is to print in the sheet "R_Purchase_Summary", all the details of the Order ( including all beneficiaries).
The problem that I have is, that sometimes the order itself has NOT yet been allocated beneficiaries names, and hence there are NO beneficiaries to be extracted when querying the "sh" sheet ( see Autofilter method , within the first With statement). When there are NO beneficiaries it prints the whole filtered list instead of "nothing" (blank) , as it should be.
I've tried the 'On error" ( but is NOT really an error)..... I also tried Specialcells , but i am not quite sure to make it work.
Hope i was clear with my explanation , otherwise please let me know. Many Thanks
as You have already noted few times
>>> use code - tags <<<
>>> use code - tags <<<
Code:
Private Sub CmdbuttonPurchaseSummary_Click()
Application.ScreenUpdating = False
Dim sh As Worksheet
Dim dsh As Worksheet
Dim shOrders As Worksheet
Dim ordernumber As Integer
Dim fnd As Range
Set sh = ThisWorkbook.Sheets("Beneficiaries_Burials")
Set dsh = ThisWorkbook.Sheets("R_Purchase_Summary")
Set shOrders = ThisWorkbook.Sheets("Orders")
'*** The code starts by requesting an order number (ordernumber), to be extracted from the shOrders sheet and also checks if the Order number exists ****
ordernumber = Application.InputBox(Prompt:="Enter Order Number", Type:=1)
Set fnd = shOrders.Range("B:B").Find(ordernumber, LookIn:=xlValues, Lookat:=xlWhole)
If fnd Is Nothing Then
MsgBox ("Order Number not found. Please try again.")
Exit Sub
End If
dsh.Range("C21:U44").ClearContents
'**** Here the code copies all the records found in sh worksheet that need to be copied to be pasted in the dsh sheet within the next With statement ****
With sh
.AutoFilterMode = False
.Range("Beneficiaries_Burials").AutoFilter Field:=2, Criteria1:=ordernumber
.Range("Beneficiaries_Burials").Copy
.AutoFilterMode = False
End With
'*** Here the code pastes in the dsh sheet, the data filtered from the sh sheet above *****
With dsh
.Range("C11").Value = ordernumber
.Range("C21").PasteSpecial xlPasteValues
End With
If sh.Range("Beneficiaries_Burials").AutoFilter Then
sh.Range("Beneficiaries_Burials").AutoFilter
End If
Application.ScreenUpdating = True
End Sub
Last edited by a moderator: