Newbie Challenged by Excel Data Entry form

[Makucha]

Hi All,

I've only recently started working with Excel VBA, and am still a bit green behind the ears. I'm modifying a spreadsheet Data Entry for sales staff users to enter data via spreadsheet form. The form contains 107 data rows of numeric, string and date/time data.

When a user runs the UpdateDBWorksheet() macro the data records from the data entry sheet are appended to the Data2 worksheet tab.

The VBA macro I've been using is great for adding up to 64 rows of data, but greater than this is generates the following error -

Run-time error '1004';

Method 'Range' of object ' Worksheet' failed
​

I think that the issue with the data type declaractions, but I'm really no sure. Any assistance would be appreciated. It needs to work in both Excel 2003 and Excel 2007.

Can someone take a look and give me hand.

Please see the code below -

Sub UpdateDBWorksheet()

Dim historyWks As Worksheet
Dim inputWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myCopy As String
'Dim myCopy As Variant
Dim myCell As Range

'Dim myRng As Variant
'Dim myCopy As Variant
'Dim myCell As Variant

'myCopy = "J5,J6,J7,J8,J9,J10,J11,J13,J14,J15,J16,J17,J18,J19,J20,J21,J22,J23,J24,J25,J26,J27,J28,J29,J30,J32,J33,J34,J35,J36,J37,J38,J40,J41,J45,J46,J47,J48,J49,J50,J51,J52,J53,J54,J55,J56,J57,J58,J59,J60,J61,J62,J63,J64,J65,J66,J67,J68,J69,J70,J71,J72,J73,J74"
'J75,J76,J77,J78,J79,J80,J81,J82,J83,J84,J85,J86,J87,J88,J89,J90,J91,J92,J93,J94,J95,J96,J97,J98,J99,J100,J101,J102,J103,J104,J105,J106,J107,J108,J109,J110,J111,J112,J113,J114,J115,J119,J120"


myCopy = "J5,J6,J7,J8,J9,J10,J11,J13,J14,J15,J16,J17,J18,J19,J20,J21,J22,J23,J24,J25,J26,J27,J28,J29,J30,J32,J33,J34,J35,J36,J37,J38,J40,J41,J45,J46,J47,J48,J49,J50,J51,J52,J53,J54,J55,J56,J57,J58,J59,J60,J61,J62,J63,J64,J65,J66,J67,J68,J69,J70,J71,J72,J73,J74,J75,J76,J77,J78,J79,J80,J81,J82,J83,J84,J85,J86,J87,J88,J89,J90,J91,J92,J93,J94,J95,J96,J97,J98,J99,J100,J101,J102,J103,J104,J105,J106,J107,J108,J109,J110,J111,J112,J113,J114,J115,J119,J120"

Set inputWks = Worksheets("Data Entry Form")
Set historyWks = Worksheets("Data2")

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

With inputWks
Set myRng = .Range(myCopy)

'Set myRng = .Variant(myCopy)
If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With

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

'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
End Sub


​