Help With Copying a column

[Kentucky]

Hi Guys

I want to copy the values in 1 column to another and then clear it when the next race is selected

What I need is

When Cell AA1=1 (this cell is a coundown timer

Copy the Values from AA5:AA34 to AN5:AN35 (I only want to copy the values from AA5:AA34 not the formulas)

AA5:AA34 is a calculation that will change as the race progresses
I want to know what it is at the start of the Race this would be copied to AN5:AN34 so that I can compare the 2 figures during the race

When the next race is selected I then want to CLEAR the values in AN5:AN34 and repeat the process for the next race

Any Help would be appreciated

Thanks

Ken

[osknows]

This should work. Rather than CLEAR the range when the race changes it clears the range when coundown timer is greater than 1

Code: Select all

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
   
   
With ThisWorkbook.Sheets(Target.Worksheet.Name)

If .Range("AA1") = 1 Then

.Range("AN5:AN35").Value = .Range("AA5:AA35").Value

ElseIf .Range("AA1") > 1 Then
.Range("AN5:AN35").ClearContents

End If
End With
   
Application.EnableEvents = True
End Sub


[Kentucky]

Thanks Osknows just what I was looking for

[Shaun]

I thought i would use this code but having trouble getting it to work, the idea is when cell S2 is populated with a 1 the macro will record the cells then paste them on another sheet, each time it pastes the infor it will past it under the old info but it won't fire, i have this in the control sheet at the moment.

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False


With ThisWorkbook.Sheets(Target.Worksheet.Name)

If Range("S2") = 1 Then
Range("A8:P55").Select
Selection.Copy
Sheets("Record").Range("A1").Select
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial (xlPasteValues)
Sheets("Control").Range("A1").Select
Application.CutCopyMode = False
End If
End With
Application.EnableEvents = True
End Sub

[osknows]

It's likely your control sheet doesn't have any cells being changed so the worksheet_change event won't fire. Change it to a a worksheet_calculate event so it fires when the control sheet calcs update.

Better still move the code to the sheet event linked to BA and paste to a new sheet on each refresh

[Shaun]

Cell S2 has a formula in it that is 0 untill a specific time then changes to 1 at that time all bets are made, i just want to record this information when the bets are made.

[osknows]

Code: Select all

Private Sub Worksheet_calculate()
Dim ba_array() As Variant

Application.EnableEvents = False

If ThisWorkbook.Sheets("Control").Range("S2").Value = 1 Then
ba_array = ThisWorkbook.Sheets("Control").Range("A8:P55").Value

With ThisWorkbook.Sheets("Record")
.Range(.Range("A" & .Range("A" & Rows.Count).End(xlUp).Row).Offset(1, 0), _
.Range("A" & .Range("A" & Rows.Count).End(xlUp).Row).Offset(UBound(ba_array, 1) - 1, _
UBound(ba_array, 2) - 1)).Value = ba_array
End With

End If

Application.EnableEvents = True
End Sub


[Shaun]

Why do macros have to be so difficult still no good, will run if i do it manually but not on it's own

[Shaun]

Sorry seans to work but is not putting text under neath but over the top.

[osknows]

Cell S2 has a formula in it that is 0 untill a specific time then changes to 1 at that time all bets are made, i just want to record this information when the bets are made.

If you place the code in the same sheet object as the S2 cell containing the formula the worksheet_calculate will fire after the worksheet is recalculated, for the worksheet object.

[Shaun]

This code works now except it fires twice, once when the cell changes to 1 then again when it changes back, unless you have a fix for that i can live with it.

Code: Select all

Private Sub Worksheet_calculate()
Static MyMarket As Variant
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
If [S1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [S1].Value

ba_array = ThisWorkbook.Sheets("Control").Range("A8:P55").Value

With ThisWorkbook.Sheets("Recorded")

Dim LastRow As Long
LastRow = .Range("A65536").End(xlUp).Row

.Range(.Cells(LastRow + 1, 1), .Cells(LastRow + 1 + UBound(ba_array, 1), 1 + UBound(ba_array, 2))).Value = ba_array

End With

End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub



[osknows]

It's because you've removed the IF statement testing if S1 = 1

"If ThisWorkbook.Sheets("Control").Range("S2").Value = 1 Then"