Needing Macros made to copy data from two sheets to one and sorted

[khelp11]

I am needing two different things for this,

1. I am needing to be able to enter either a bid number or bid name and have it automatically fill in the other information from the bidders sheet for each live and silent auction sheets.

2. Needing all lines from silent and live auction sheets to copy and pull over to the check out sheet and sort by bidder number.

Auction Platform 1.xlsm
	A	B	C	D	E
1	Item #	Item Description	Bidder #	Buyer	Winning Bid
2	1	earrings	1
3	3	cowboy art	4
4	4	car	6
5	8	t-shirt	8
6	9	tanktop	4
7	10	shoes	11
8	11	blue board	5
9	16	rug	7
10	18	elk hunt	20
11	20	phone	18
12	42	lamp	4
Silent

Auction Platform 1.xlsm
	A	B	C	D	E
1	Item #	Item Description	Bidder #	Buyer	Winning Bid
2	2	necklace	9
3	5	chair	8
4	6	frame	4
5	7	board	6
6	12	pink frame	14
7	13	dresser	1
8	14	elk horn	5
9	15	deer mount	7
10	17	buffalo hide	8
11	19	buffalo hunt	15
12	21	sign	13
Live

Auction Platform 1.xlsm
	A	B	C
1	Bid Number	Name	Phone Number
2	1	joey	(287) 201-8233
3	2	cody	(111) 115-5533
4	3	krys	(345) 666-9999
5	4	sid	(199) 999-9983
6	5	di	(209) 129-3841
7	6	jame	(111) 111-1111
8	7	aie	(333) 333-3333
9	8	aaaa	(228) 486-6047
10	9	kjsdfie	(228) 552-6058
11	10	ksdjfi	(228) 618-6069
12	11	lsdoi	(228) 684-6079
13	12	ksjdfi	(228) 750-6090
14	13	stu	(228) 816-6101
15	14	bo	(228) 882-6111
16	15	toe	(228) 948-6122
17	16	moe wkj	(229) 014-6132
18	17	eeed	(229) 080-6143
19	18	asd	(229) 146-6154
20	19	sdkji	(229) 212-6164
21	20	kjsdi	(229) 278-6175
Bidders

Auction Platform 1.xlsm
	A	B	C	D	E	F	G
1	Item #	Item Description	Buyer	Bidder #	Winning Bid	Amount PAID	payment type
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Check out

Cells with Data Validation
Cell	Allow	Criteria
G1:G22	List	=$L$3:$L$5

 

[mse330]

Try below code ...

Sub test()

Dim Txt$, Col&
Txt = InputBox("Please enter specific bid # or bid name or ALL")

If Len(Txt) = 0 Then
    MsgBox "No bidder data was entered, macro will be stopped", vbExclamation
    Exit Sub
End If

Sheets("Check out").[A1].CurrentRegion.Offset(1).Clear
Col = IIf(Val(Txt), 1, 2)

For Each e In Array("Silent", "Live")
    With Sheets(e).[A1].CurrentRegion
        If UCase(Txt) = "ALL" Then
            .Offset(1).Copy Sheets("Check out").Range("A" & Rows.Count).End(3)(2)
        Else
            .AutoFilter Col, Txt
            .Offset(1).Copy Sheets("Check out").Range("A" & Rows.Count).End(3)(2)
            .AutoFilter
        End If
    End With
Next

If UCase(Txt) = "ALL" Then Sheets("Check out").[A1].CurrentRegion.Sort [A1], 1, Header:=xlYes

End Sub

 

[khelp11]

It did not work but it said error 1004 something about sort by.

 

[mse330]

If the error is only related to sorting then it should completed all the code lines without error to transfer the data & the error is only related to sorting, can you confirm ?

 

[khelp11]

So, I went to run the macro "test". then it comes up with the box below.

If I enter ALL then it comes up with he following box

I do not need or know what the box saying "Please enter specific bid # or name or ALL". I am only wanting to be able to enter either a bid number or bid name in the silent and live auction tabs and have the other information from the bidders sheet automatically fill in.

Then, I want all the rows in the silent and live auction information automatically pulled over to the check out sheet and then sort them by the bidder number.