HELP! Need to copy contents of W5:W50 to AA5:AA50 ONCE

[danjuma]

There's probably a thread about this somewhere in the forum. I will keep looking. In the meantime, if somebody could oblige please with a code, I would be most grateful.

Many thanks

[mak]

how about this?you can modified a little and it is ready (i think )

http://www.gruss-software.co.uk/forum/v ... light=copy

[danjuma]

Thanks mak.

Did see that, but looks a bit complicated for me to understand for what I want. I managed to piece the following code together, but unfortunately, it keeps coping new data at every refresh, I want to capture the data just once. I think I need to figure out how to code/where to place the refreshCounter Any suggestions please?

Code: Select all

Dim refreshCount As Integer

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)
refreshCount = 0
If refreshCount = 0 Then
 Range("aa5:aa50").Value = Range("w5:w50").Value
End If
 refreshCount = refreshCount + 1
End With
Application.EnableEvents = True
End Sub

[Captain Sensible]

Problem with your current code is that it's always resetting

refreshCount = 0

at the start of the coding , so refreshCount is always 0 and the code will continue to copy.

You need to be looking at whats the criteria for triggering that 'capture' and then stick the

refreshCount = 0

within that part of the code.

When is it meant to capture?

[danjuma]

Problem with your current code is that it's always resetting

refreshCount = 0

at the start of the coding , so refreshCount is always 0 and the code will continue to copy.

You need to be looking at whats the criteria for triggering that 'capture' and then stick the

refreshCount = 0

within that part of the code.

When is it meant to capture?

Hi CS,

and thanks for the reply. I basically want want it to capture when the first bets are placed. So basically capture when "sum(w5:w50)>0".
Anyway, if I can't come up with a solution, I might go about it in a round about way using "MyBets" sheet.

[Captain Sensible]

You could use something like WorksheetFunction.Sum(Range("W5:W50")) and reset the values when the market changes. You'd obviously miss any matches being logged after the first matched bet though




Dim refreshCount As Currency
Dim betCount As Currency

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)

betCount = WorksheetFunction.Sum(Range("W5:W50"))


If betCount = 0 Then
refreshCount = 0
Range("AA5:AA50").Value = ""
End If


If betCount > 0 And refreshCount < 1 Then
Range("aa5:aa50").Value = Range("w5:w50").Value
refreshCount = refreshCount + 1
End If

End With
Application.EnableEvents = True
End Sub

[danjuma]

You could use something like WorksheetFunction.Sum(Range("W5:W50")) and reset the values when the market changes. You'd obviously miss any matches being logged after the first matched bet though




Dim refreshCount As Currency
Dim betCount As Currency

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)

betCount = WorksheetFunction.Sum(Range("W5:W50"))


If betCount = 0 Then
refreshCount = 0
Range("AA5:AA50").Value = ""
End If


If betCount > 0 And refreshCount < 1 Then
Range("aa5:aa50").Value = Range("w5:w50").Value
refreshCount = refreshCount + 1
End If

End With
Application.EnableEvents = True
End Sub

Many thanks for this CS. much obliged.

"You'd obviously miss any matches being logged after the first matched bet though" That's fine, just need to capture when the first bet(s) are placed. Cheers

[danjuma]

That's fine, just need to capture when the first bet(s) are placed.

Sorry meant matched.