VBA To Copy & Paste Value

VinceF

VinceF

Board Regular
Joined
Sep 22, 2007
Messages
189
Office Version
  1. 2016
Platform
  1. Windows
Greetings...Another tough one at least for me.
This is a Golf game and it'll track winnings or losses for 40 rounds.
I have a workbook with several sheets. Only two of them come into play with this situation, "MAIN" & "WIN".
On the "MAIN" sheet I think I'd need a command button or active x (not sure) that triggers which column to copy and paste values on the "WIN" sheet.
On the MAIN sheet in cell AA3 will be a number between 1 and 40 representing which round is being played. This number in cell MAIN AA3 will dictate which column on the WIN sheet to copy and paste values.
On the WIN sheet #1 from the MAIN sheet (cell AA3) corresponds to column E, #2 corresponds to column F and so on until #40 corresponds to column AR
By copying and pasting the values it locks in the numbers in that column so when I save and close the workbook I don't lose those values going into the next round.

You'll see that in this example that the WIN sheet shows Round#5 in cell (WIN B4) was played and the button from the MAIN sheet would execute the copy and paste function in cells I2:I144

Thanks for your time and expertise...
VinceF
Excel 2016

1-16 test INDIANWOOD QUOTA - SKINS.xlsm
ABCDEFGHIJKLMN
1xxx
2RETURN TO MAIN PAGEENTRY FEE    $60     
3ROUND #TOP MONEY WINNERGAME TYPE    Q & S     
45ADAM PIOCH
5TODAYPLAYERYEAR TO DATE
6WON/LOSTEARNINGS12345678910
7$10STAFF$10    $10     
8 ADAM ALTMAN           
9$95ADAM PIOCH$35    $35     
10 ALLEN PRAET          
11$55ANTHONY STEFANI-$5    -$5     
12 AUSTIN ROMZEK           
13$20BILL KNIGHT-$40   -$40     
14 BILLY UHL           
15 BRANDON LESKE           
16 BRIAN KNOX           
Win
Cell Formulas
RangeFormula
E2:N2E2=IF($B$4<>E$6,"",IF($B$4=E$6,SUM(Main!$D$3:$F$3)))
E3:N3E3=IF($B$4<>E$6,"",IF(AND($B$4=E$6,Main!$C$3="QUOTA & SKINS"),"Q & S", IF(AND($B$4=E$6,Main!$C$3="QUOTA"),"QUOTA", IF(AND($B$4=E$6,Main!$C$3="SKINS"),"SKINS"))))
B4B4=Main!$AA$3
C4C4='Top 10'!F8
B7B7=Main!$L$6
D7D7=SUM(E7:AQ7)
E7:N7E7=IF($B$4<>E$6,"",IF($B$4=E$6,Main!$L$6))
J8:J16J8=IFERROR(IF($B$4<>$J$6,"",IF($B$4=$J$6,INDEX(Main!$E$11:$E$50,MATCH(C8,Main!$G$11:$G$50,0))))-$J$2,"")
K8:K16K8=IFERROR(IF($B$4<>$K$6,"",IF($B$4=$K$6,INDEX(Main!$E$11:$E$50,MATCH(C8,Main!$G$11:$G$50,0))))-$K$2,"")
L8:L16L8=IFERROR(IF($B$4<>$L$6,"",IF($B$4=$L$6,INDEX(Main!$E$11:$E$50,MATCH(C8,Main!$G$11:$G$50,0))))-$L$2,"")
M8:M16M8=IFERROR(IF($B$4<>$M$6,"",IF($B$4=$M$6,INDEX(Main!$E$11:$E$50,MATCH(C8,Main!$G$11:$G$50,0))))-$M$2,"")
N8:N16N8=IFERROR(IF($B$4<>$N$6,"",IF($B$4=$N$6,INDEX(Main!$E$11:$E$50,MATCH(C8,Main!$G$11:$G$50,0))))-$N$2,"")
B8:B16B8=IFERROR(INDEX(Main!$E$11:$E$50,MATCH(C8,Main!$G$11:$G$50,0)),"")
C8:C16C8=IFERROR(INDEX('Player Quota History'!$D$5:$D$141,MATCH('Player Quota History'!D5,'Player Quota History'!$D$5:$D$141,0)),"")
D8:D16D8=IF(SUM(E8:AQ8)=0,"",SUM(E8:AQ8))
E8:E16E8=IFERROR(IF($B$4<>$E$6,"",IF($B$4=$E$6,INDEX(Main!$E$11:$E$50,MATCH(C8,Main!$G$11:$G$50,0))))-$E$2,"")
F8:F16F8=IFERROR(IF($B$4<>$F$6,"",IF($B$4=$F$6,INDEX(Main!$E$11:$E$50,MATCH(C8,Main!$G$11:$G$50,0))))-$F$2,"")
G8:G16G8=IFERROR(IF($B$4<>$G$6,"",IF($B$4=$G$6,INDEX(Main!$E$11:$E$50,MATCH(C8,Main!$G$11:$G$50,0))))-$G$2,"")
H14:H16,H8:H12H8=IFERROR(IF($B$4<>$H$6,"",IF($B$4=$H$6,INDEX(Main!$E$11:$E$50,MATCH(C8,Main!$G$11:$G$50,0))))-$H$2,"")
I11:I16,I8:I9I8=IFERROR(IF($B$4<>$I$6,"",IF($B$4=$I$6,INDEX(Main!$E$11:$E$50,MATCH(C8,Main!$G$11:$G$50,0))))-$I$2,"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E8:E144Expression=AND(ISNUMBER(B8),$B$4=1)textNO
E7Expression=AND(ISNUMBER(B7),$B$4=1)textNO
E6Expression=$B$4=1textNO
G8:G144Expression=AND(ISNUMBER(B8),$B$4=3)textNO
G7Expression=AND(ISNUMBER(B7),$B$4=3)textNO
G6Expression=$B$4=3textNO
F8:F144Expression=AND(ISNUMBER(B8),$B$4=2)textNO
F7Expression=AND(ISNUMBER(B7),$B$4=2)textNO
F6Expression=$B$4=2textNO
N8:N144Expression=AND(ISNUMBER(B8),$B$4=10)textNO
N7Expression=AND(ISNUMBER(B7),$B$4=10)textNO
M8:M144Expression=AND(ISNUMBER(B8),$B$4=9)textNO
M7Expression=AND(ISNUMBER(B7),$B$4=9)textNO
L8:L144Expression=AND(ISNUMBER(B8),$B$4=8)textNO
L7Expression=AND(ISNUMBER(B7),$B$4=8)textNO
K8:K144Expression=AND(ISNUMBER(B8),$B$4=7)textNO
K7Expression=AND(ISNUMBER(B7),$B$4=7)textNO
J8:J144Expression=AND(ISNUMBER(B8),$B$4=6)textNO
J7Expression=AND(ISNUMBER(B7),$B$4=6)textNO
I8:I144Expression=AND(ISNUMBER(B8),$B$4=5)textNO
I7Expression=AND(ISNUMBER(B7),$B$4=5)textNO
H8:H144Expression=AND(ISNUMBER(B8),$B$4=4)textNO
H7Expression=AND(ISNUMBER(B7),$B$4=4)textNO
N6Expression=$B$4=10textNO
M6Expression=$B$4=9textNO
L6Expression=$B$4=8textNO
K6Expression=$B$4=7textNO
J6Expression=$B$4=6textNO
I6Expression=$B$4=5textNO
D8:D144Expression=ISNUMBER($B8)textNO
D7Cell Value>-1textNO
D7Expression=ISNUMBER($B7)textNO
C7Cell Value>=0textNO
B7Cell Value>=0textNO
C8:C144Expression=ISNUMBER(B8)textNO
B8:B144Expression=ISNUMBER(B8)textNO
H6Expression=$B$4=4textNO
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
You must log in or register to reply here.

Similar threads

VinceF
  • Solved
Circular Reference Error
2
Replies
10
Views
749
mumps
mumps
VinceF
  • Solved
Copy/Paste Value Only If The Cell Contains A Number
Replies
9
Views
820
smozgur
smozgur
VinceF
  • Question Question
Circular Reference Error
Replies
3
Views
391
VinceF
VinceF
VinceF
  • Question Question
Circular Reference Error
Replies
8
Views
591
VinceF
VinceF
VinceF
  • Solved
Code to Disregard VBA or to Execute if Condition Met
Replies
3
Views
235
Alex Blakenburg
A

Forum statistics

Threads
1,223,711
Messages
6,174,028
Members
452,542
Latest member
Bricklin

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