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

K

khelp11

New Member
Joined
Mar 13, 2024
Messages
11
Office Version
  1. 2011
Platform
  1. Windows
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
ABC
1Bid NumberNamePhone Number
21joey(287) 201-8233
32cody(111) 115-5533
43krys(345) 666-9999
54sid(199) 999-9983
65di(209) 129-3841
76jame(111) 111-1111
87aie(333) 333-3333
98aaaa(228) 486-6047
109kjsdfie(228) 552-6058
1110ksdjfi(228) 618-6069
1211lsdoi(228) 684-6079
1312ksjdfi(228) 750-6090
1413stu(228) 816-6101
1514bo(228) 882-6111
1615toe(228) 948-6122
1716moe wkj(229) 014-6132
1817eeed(229) 080-6143
1918asd(229) 146-6154
2019sdkji(229) 212-6164
2120kjsdi(229) 278-6175
Bidders



Auction Platform 1.xlsm
ABCDE
1Item #Item DescriptionBidder #BuyerWinning Bid
22necklace9
35chair 8
46frame4
57board6
612pink frame14
713dresser1
814elk horn5
915deer mount7
1017buffalo hide8
1119buffalo hunt15
1221sign13
Live


Auction Platform 1.xlsm
ABCDE
1Item #Item DescriptionBidder #BuyerWinning Bid
21earrings1
33cowboy art4
44car6
58t-shirt8
69tanktop4
710shoes11
811blue board5
916rug7
1018elk hunt20
1120phone18
1242lamp4
Silent


Auction Platform 1.xlsm
ABCDEFG
1Item #Item DescriptionBuyerBidder #Winning BidAmount PAIDpayment type
21earrings1
33cowboy art4
44car6
58t-shirt8
69tanktop4
710shoes11
811blue board5
916rug7
1018elk hunt20
1120phone18
1242lamp4
132necklace9
145chair 8
156frame4
167board6
1712pink frame14
1813dresser1
1914elk horn5
2015deer mount7
2117buffalo hide8
2219buffalo hunt15
2321sign13
Check out
Cells with Data Validation
CellAllowCriteria
G1List=$L$3:$L$5
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Hello Khelp11, can you show an example of 5 results for your question? I'm not understanding your request. cheers!
 
Upvote 0
I want to be able to enter in a bidder # into my silent and live action pages and have it pull the other information that corresponds to that number in the bidder sheet to pull over. and then automatically copies all live and silent auction information over to the check out sheet.
 
Upvote 0
try this
VBA Code: 
Sub macro1_all()
    Application.Run "macro2"
    Application.Run "macro3"
    Application.Run "macro4"
    Application.Run "macro5"
    Application.Run "macro6"
    Application.Run "macro7"
End Sub



Sub Macro2()
''''assumes column A is always populated.
'''find last row in the sheet, to be used as reference for copy and paste later
'''cut and paste the value to cell Z1, so it is a static reference of cell Z1
'''in Z2 Z3, use a minus one and minus two because the number of rows you use later will be minus 2 of the total rows
    Sheets("Live").Select
    Application.Goto Reference:="R999999C1"
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.Copy
    Selection.Clear
    Selection.FormulaR1C1 = "=ROW()"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Selection.Cut
    Application.Goto Reference:="R1C26"
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C-1"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C-1"
    ActiveCell.Offset(1, 0).Range("A1").Select

    Sheets("Silent").Select
    Application.Goto Reference:="R999999C1"
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.Copy
    Selection.Clear
    Selection.FormulaR1C1 = "=ROW()"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Selection.Cut
    Application.Goto Reference:="R1C26"
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C-1"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C-1"
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Sub Macro3()
'bring in headers from your Bidder Sheet in H1
    Sheets("Live").Select
    Application.Goto Reference:="R1C8"
    Selection.FormulaR1C1 = "=Bidders!RC[-7]"
''' bring in A to O, as you probably have a lot more information than your sample
    Selection.Copy
    Range("H1:V1").Select
    ActiveSheet.Paste

'bring in headers from your Bidder Sheet in H1
    Sheets("Silent").Select
    Application.Goto Reference:="R1C8"
    Selection.FormulaR1C1 = "=Bidders!RC[-7]"
''' bring in A to O, as you probably have a lot more information than your sample
    Selection.Copy
    Range("H1:V1").Select
    ActiveSheet.Paste

End Sub
Sub Macro4()
'''set up Lookup formulas to bring in data from Bidder sheet
    Sheets("Live").Select
    Application.Goto Reference:="R2C8"
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-7]:C[18],MATCH(RC[-5],Bidders!C[-7],0),1)"
    Range("I2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-8]:C[17],MATCH(RC[-6],Bidders!C[-8],0),2)"
    Range("J2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-9]:C[16],MATCH(RC[-7],Bidders!C[-9],0),3)"
    Range("K2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-10]:C[15],MATCH(RC[-8],Bidders!C[-10],0),4)"
    Range("L2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-11]:C[14],MATCH(RC[-9],Bidders!C[-11],0),5)"
    Range("M2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-12]:C[13],MATCH(RC[-10],Bidders!C[-12],0),6)"
    Range("N2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-13]:C[12],MATCH(RC[-11],Bidders!C[-13],0),7)"
    Range("O2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-14]:C[11],MATCH(RC[-12],Bidders!C[-14],0),8)"
    Range("P2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-15]:C[10],MATCH(RC[-13],Bidders!C[-15],0),9)"
    Range("Q2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-16]:C[9],MATCH(RC[-14],Bidders!C[-16],0),10)"
    Range("R2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-17]:C[8],MATCH(RC[-15],Bidders!C[-17],0),11)"
    Range("S2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-18]:C[7],MATCH(RC[-16],Bidders!C[-18],0),12)"
    Range("T2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-19]:C[6],MATCH(RC[-17],Bidders!C[-19],0),13)"
    Range("U2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-20]:C[5],MATCH(RC[-18],Bidders!C[-20],0),14)"
    Range("V2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-21]:C[4],MATCH(RC[-19],Bidders!C[-21],0),15)"
'copy paste formulas for all the Bidders in your sheet
    Application.Goto Reference:="R2C8"
    Selection.Copy
    ActiveCell.Range("A1:O1").Select
    Selection.Copy
'''    ActiveCell.Range("A1:O11").Select.  notice this line of code is changed to use cell Z3, so it is not always static
    ActiveCell.Range("A1:O" & Range("z3")).Select
    ActiveSheet.Paste



'''set up Lookup formulas to bring in data from Bidder sheet
    Sheets("Silent").Select
    Application.Goto Reference:="R2C8"
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-7]:C[18],MATCH(RC[-5],Bidders!C[-7],0),1)"
    Range("I2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-8]:C[17],MATCH(RC[-6],Bidders!C[-8],0),2)"
    Range("J2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-9]:C[16],MATCH(RC[-7],Bidders!C[-9],0),3)"
    Range("K2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-10]:C[15],MATCH(RC[-8],Bidders!C[-10],0),4)"
    Range("L2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-11]:C[14],MATCH(RC[-9],Bidders!C[-11],0),5)"
    Range("M2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-12]:C[13],MATCH(RC[-10],Bidders!C[-12],0),6)"
    Range("N2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-13]:C[12],MATCH(RC[-11],Bidders!C[-13],0),7)"
    Range("O2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-14]:C[11],MATCH(RC[-12],Bidders!C[-14],0),8)"
    Range("P2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-15]:C[10],MATCH(RC[-13],Bidders!C[-15],0),9)"
    Range("Q2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-16]:C[9],MATCH(RC[-14],Bidders!C[-16],0),10)"
    Range("R2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-17]:C[8],MATCH(RC[-15],Bidders!C[-17],0),11)"
    Range("S2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-18]:C[7],MATCH(RC[-16],Bidders!C[-18],0),12)"
    Range("T2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-19]:C[6],MATCH(RC[-17],Bidders!C[-19],0),13)"
    Range("U2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-20]:C[5],MATCH(RC[-18],Bidders!C[-20],0),14)"
    Range("V2").Select
    Selection.FormulaR1C1 = "=INDEX(Bidders!C[-21]:C[4],MATCH(RC[-19],Bidders!C[-21],0),15)"
'copy paste formulas for all the Bidders in your sheet
    Application.Goto Reference:="R2C8"
    Selection.Copy
    ActiveCell.Range("A1:O1").Select
    Selection.Copy
'''    ActiveCell.Range("A1:O11").Select.  notice this line of code is changed to use cell Z3, so it is not always static
    ActiveCell.Range("A1:O" & Range("z3")).Select
    ActiveSheet.Paste
End Sub

Sub Macro5()
'copy to Check out, from A2 to V
    Sheets("Live").Select
    Application.Goto Reference:="R2C1"
'''    ActiveCell.Range("A1:V12").Select
    ActiveCell.Range("A1:V" & Range("z3")).Select
    Selection.Copy
    Calculate
    Selection.Copy
    Sheets("Check out").Select
    Application.Goto Reference:="R999999C1"
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'copy to Check out, from A2 to V
    Sheets("Silent").Select
    Application.Goto Reference:="R2C1"
'''    ActiveCell.Range("A1:V12").Select
    ActiveCell.Range("A1:V" & Range("z3")).Select
    Selection.Copy
    Calculate
    Selection.Copy
    Sheets("Check out").Select
    Application.Goto Reference:="R999999C1"
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub


Sub Macro6()
'''for sorting
''''assumes column A is always populated.
'''find last row in the sheet, to be used as reference for copy and paste later
'''cut and paste the value to cell Z1, so it is a static reference of cell Z1
'''in Z2 Z3, use a minus one and minus two because the number of rows you use later will be minus 2 of the total rows
    Sheets("Check out").Select
    Application.Goto Reference:="R999999C1"
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.Copy
    Selection.Clear
    Selection.FormulaR1C1 = "=ROW()"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Selection.Cut
    Application.Goto Reference:="R1C26"
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C-1"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C-1"
    ActiveCell.Offset(1, 0).Range("A1").Select

End Sub

Sub Macro7()
'sort by column C  Bidder #
    Sheets("Check out").Select
    Application.Goto Reference:="R1C1"
    Application.Goto Reference:="R2C1"
    ActiveCell.Range("A1:Y" & Range("z2")).Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add2 Key:=ActiveCell.Offset(0, 2).Range("A1:A" & Range("z2")), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange ActiveCell.Range("A1:Y" & Range("z2"))
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Needing Macros made to copy data from two sheets to one and sorted
Replies
4
Views
335
khelp11
K
K
  • Question Question
auto fill, copying and sorting sheets using macros if possible since I am already using it.
Replies
3
Views
335
Jeffrey Mahoney
Jeffrey Mahoney
E
  • Solved
Returning specific texts within a wide range of data coming from the Web
Replies
8
Views
288
Peter_SSs
Peter_SSs
J
  • Question Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
432
johnnyL
J
W
  • Question Question
Sort data with calculated fields gives unexpected results
Replies
4
Views
503
wpryan
W

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top