Not_Really_Me
New Member
- Joined
- May 29, 2020
- Messages
- 5
- Office Version
- 2016
- Platform
- Windows
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.
Any help or suggestions are greatly appreciated!!!
VBA Code:
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!!!
Last edited by a moderator: