HI There,
I cannot get the 'On error' code work properly in this procedure. Did I enter it in the 'wrong place'?
Thanking you in advance.
( sorry about the formatting, but i do not know how to enter the 'code tags' required to format the code properly.) pls help if you can .
Thanks



Private Sub CmdbuttonPurchaseSummary_Click()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Beneficiaries_Burials")
Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Purchase_Summary")
Dim ordernumber As Integer

'TryAgain:
ordernumber = Application.InputBox(Prompt:="Enter Order Number", Type:=1)

dsh.Range("C21:R400").ClearContents

sh.Activate
sh.AutoFilterMode = False
'On Error GoTo TryAgain
sh.Range("Beneficiaries_Burials").AutoFilter Field:=1, Criteria1:=ordernumber
'On Error GoTo 0
sh.Range("Beneficiaries_Burials").Copy

dsh.Activate
dsh.Range("C11").Value = ordernumber
dsh.Range("C21").PasteSpecial xlPasteValues

sh.AutoFilterMode = False
sh.ShowAllData

'Exit Sub

'TryAgain:
' MsgBox "you have entered the wrong Order Number"

End Sub

Here is your code in the proper format. You click on the # & then past the code in between the code brackets. Try it.

Many Thanks to FrancisM for sorting out my previous problem regarding 'Autofilter".

I would like to ask one more question if at all possible, please , as follows:

I have decided to use Excel's built-in' Data Entry form ( works perfectly fine for me), although i cannot use it twice ( in other words ) in two separate sheets within the same workbook. The reason is as follows:

I have inserted a Command button to call out the form as follows:
ActiveSheet.ShowDataForm
and then i was told that i had to create a range named 'Database' on that specific sheet to enable the form to open properly. It works fine, although the problem is:

If I want to use Excel's Data Entry form on another sheet of my current workbook, it will not allow me to create another range named 'Database' again.
Hope i explained myself properly.
Can you please assist ?
Thanking you in advance
Regards
Martin Argimon

I am glad that your solution worked for you. I am thinking that excel does not like duplicate names. So just like worksheets, you have to have a different name. I.e when I am calling 2 different web addresses of an site, I use IE1 & IE2. Same site, different names. That is the only solution, I can think of, unless somebody else in the group has a better idea.

Thanks FrancisM. Indeed Excel does not like duplicate names that is why i was battling to understand this option, "of calling the Form via VBA and then naming a range 'Database' and not being able to call the Data form on another sheet?
I find it strange that it can only work on one sheet. I shall wait to hear if anybody else has a suggestion
Many thanks again
Kind Regards
Martin Argimon
Kind regards