Macro is consistently inconsistent

[Not_Really_Me]

I'm entering data from a variety of different responses, the data for which are all stored in excel worksheets. To make it easier, I put together a form (in the code, "Data Entry") which allows me to paste in the data, click a button and have the data automatically copied over into a master worksheet ("Data Collected"). For the most part, this works perfectly, but out of 770 cells there are 8 that it consistently fails to copy to the "Data Collected" worksheet. I've tried using different formatting and different source cells where the data are being pulled from, but it's always the same 8 cells. Pasted below is the relevant code. In myCopy, the cells that are currently being dropped are H5:H12, though the same data are transferred correctly when the section is repeated at the end (H5:H90). Even weirder, when I run the macro from the code viewer, the data appear in those cells briefly, but then disappear after a second or two.

Option Explicit

Sub UpdateDataCollected()

Dim EntryPg As Worksheet
Dim DataColl As Worksheet
Dim myCopy As String
Dim nextRow As Long
Dim oCol As Long
Dim myRng As Range
Dim myCell As Range

myCopy = "B3,E3,B5,E5,B7,H5:H90,I5:I90,J5:J90,K5:K90,L5:L90,M5:M90,N5:N90,O5:O90,H5:H90"

Set EntryPg = Worksheets("Data Entry")
Set DataColl = Worksheets("Data Collected")

With DataColl
    nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With EntryPg
    Set myRng = .Range(myCopy)

End With

With DataColl
    With .Cells(nextRow, "A")
        .Value = Now
        .NumberFormat = "mm/dd/yyyy"
    End With
    .Cells(nextRow, "B").Value = Application.UserName
    oCol = 3
    For Each myCell In myRng.Cells
        DataColl.Cells(nextRow, oCol).Value = myCell.Value
        oCol = oCol + 1
    Next myCell
End With

With DataColl
    On Error Resume Next
        With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
            .ClearContents
            Application.GoTo .Cells(1)
        End With
    On Error GoTo 0
End With

End Sub

Any help or suggestions are greatly appreciated!!!

 

[Fluff]

There is nothing inconsistent that I can see, it's just that you are telling it to clear cells "B3,E3,B5,E5,B7,H5:H90,I5:I90,J5:J90,K5:K90,L5:L90,M5:M90,N5:N90,O5:O90,H5:H90" if they contain hard values.

 

[GWteB]

No clou at present but you might try the use of a named range instead of the "myCopy" string.

 

[Not_Really_Me]

There is nothing inconsistent that I can see, it's just that you are telling it to clear cells "B3,E3,B5,E5,B7,H5:H90,I5:I90,J5:J90,K5:K90,L5:L90,M5:M90,N5:N90,O5:O90,H5:H90" if they contain hard values.

Sorry, I'm fairly inexperienced with using macros, what do you mean about clearing cells if they contain hard values?

 

[Fluff]

At the end of your code you have

With DataColl
    On Error Resume Next
        With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
            .ClearContents
            Application.GoTo .Cells(1)
        End With
    On Error GoTo 0
End With

Which is clearing the cells "B3,E3,B5,E5,B7,H5:H90,I5:I90,J5:J90,K5:K90,L5:L90,M5:M90,N5:N90,O5:O90,H5:H90" is they contain values, rather than formulae.
I suspect it should be looking at the other sheet.

 

[Not_Really_Me]

At the end of your code you have

With DataColl
    On Error Resume Next
        With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
            .ClearContents
            Application.GoTo .Cells(1)
        End With
    On Error GoTo 0
End With

Which is clearing the cells "B3,E3,B5,E5,B7,H5:H90,I5:I90,J5:J90,K5:K90,L5:L90,M5:M90,N5:N90,O5:O90,H5:H90" is they contain values, rather than formulae.
I suspect it should be looking at the other sheet.

That was it!!! Thank you so much for point that out, cleared out that section of the code, tried it with a few sample data sets and it consistently works consistently now!

Out of curiosity, can you tell why was it clearing out just those 8 cells?

 

[Fluff]

I suspect that the intention was to clear the cells on the Data Entry sheet, but you were clearing them on the Collection sheet instead.