VBA to Copy paste value based on value in cell

[VinceF]

I posted a similar question earlier but I assume that I didn't elaborate clearly enough.

Cell AA3 on the "MAIN" page contains the # "1" This number will increase by 1 as each round of golf is played...up to 40
On the MAIN page I have a command button. The button has column E2:E144 on the "WIN" sheet to copy and paste values.

What do I need to add or how do I modify the VBA to have it look at the cell in MAIN AA3 and based on that number execute the following.
1 = copy and paste values on WIN E2:E144
2= copy and paste values on WIN F2:F144
3= copy and paste values on WIN G2:G144
all the way to 40= copy and paste values on AR2:AR144

Thanks for any assistance
VinceF
Excel 2016


Sub LockR1()
'
' LockR1 Macro
'

'
Sheets("Win").Select
Range("E2:E144").Select
Selection.Copy
Selection.PasteSpecial PASTE:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 1
Range("E6").Select
Application.CutCopyMode = False
Sheets("Main").Select
Range("AK5").Select
End Sub

 

[DanteAmor]

Try this:

Sub copy_and_paste()
  Dim n As Long
  Application.ScreenUpdating = False
  n = Sheets("MAIN").Range("AA3").Value
 
  With Sheets("WIN").Cells(2, 4 + n).Resize(143)
    .Copy
    .PasteSpecial xlPasteValues
    Application.CutCopyMode = False
  End With
End Sub

Or this:

Sub copy_and_paste()
  With Sheets("WIN").Cells(2, 4 + Sheets("MAIN").Range("AA3").Value).Resize(143)
    .Value = .Value
  End With
End Sub

 

[VinceF]

Dante.... I cant thank you enough...it's working almost perfectly...!
The only issue with it is that it leaves the column that it copied and pasted highlighted or selected.
Although it's not a big deal but if you have a line or two that I can add to the code to resolve this it'd be appreciated.


Again....very, very much appreciated
VinceF

 

[DanteAmor]

After this line
Application.CutCopyMode = False

Add these lines:
Sheets("WIN").Select
Range("A1").Select
Sheets("MAIN").Select

----- --
You should also try the second macro that I put in post #2

 

[VinceF]

Thank you Dante...
it's working absolutely perfect now....I very much appreciate how quickly and excellently you solved my problem.
I'll mess with the other code as you suggested....

Thanks again,
VinceF