Help on an Excel VBA run time error 1004

[Tarmac]

Hi - I'd appreciate some help on a VBA macro I'm using which won't work as I had hoped because it creates a run time error 1004.

Firstly, I know nothing about VBA, a friend created the code, but said the error was specific to the software I'm running ie BA.

The code is basically a copy/paste/values after the end of the race, as follows..

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wks As Worksheet, wks1 As Worksheet
Set wks = Worksheets("A")
Set wks1 = Worksheets("ResA") '

If Not Target.HasFormula Then
Set Rng = Target.Dependents
If Not Intersect(Range("AB4"), Rng) Is Nothing Then
If wks.Range("AB4").Value = "END" Then
wks.Range("A5:AG30").Copy
wks1.Range(Range("AD3").Value).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
End If
End If
End Sub

I get the error message each time I move to a new market (either manually or auto load market) at the "Set Rng = Target.Dependents" point above

Can anyone help in either a) amending the code so I don't get this message, or b) give a workaround so I don't have to OK the error message each time I change market. btw - the "END" at AB4 is result of an IF statement (not sure if that affects anything!)

Thanks very much.

Tarmac

[osknows]

Put this in the sheet object of sheet Worksheets("A"):

Code: Select all

Option Explicit

Dim currentMarket As String
Dim dataCopied As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wks1 As Worksheet
Dim nxtRow As Long
   
    If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False
   
    With Target.Parent
        If .Range("A1").Value <> currentMarket Then
             currentMarket = .Range("A1").Value
             dataCopied = False
         End If
           
        If .Range("AB4").Value = "END" Then
            If dataCopied = False Then
                dataCopied = True
                Set wks1 = Worksheets("ResA")
                nxtRow = LastRow(wks1.Range("A:AG"), 1)
                wks1.Range("A1:AG26").Offset(nxtRow - 1, 1).Value = .Range("A5:AG30").Value
            End If
        End If
    End With
    Application.EnableEvents = True
End Sub

and this in a MODULE:

Code: Select all

Public Function LastRow(ByVal rng As Range, Optional Offset As Long) As Long
'The most accurate method to return last used row in a range.
On Error GoTo blankSheetError
    'Identify next blank row
    LastRow = rng.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row + Offset

    'On Error GoTo 0 'not really needed
    Exit Function

blankSheetError:
    LastRow = 1 'Will produce error if blank sheet so default to row 1  as cannot have row 0
    Resume Next

End Function

It will then copy Range("A5:AG30") of sheet "A" to the next blank row in sheet "ResA" once only when cell .Range("AB4").Value = "END"

[Tarmac]

Osknows. Sorry for delay in acknowledging your reply. Thanks very much for taking the time to find a solution to the issue I raised.

May you clean out Betfair!

[Tarmac]

I am trying to have the bet refs stored in col Z to be picked up on col T (bet reference) if cell AA1 = 1.

Below is as far as I've got! Can anyone please advise what code should be. Many thanks

T

Private Sub Worksheet_Calculate()

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

Range("Z5:Z200").Copy Range("T5:T200")


End Sub

[rourkem]

Giving this a soft bump please. Anyone?