help of VBA

[xiaodan86]

I have having an input sheet(Input) and use macro to add data to
another sheet(PartsData)
The macro is as below:

Sub UpdateLogWorksheet()

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 myCell As Range

======================================================================
'cells to copy from Input sheet - some contain formulas
myCopy =
"C2,G2,G3,G4,G5,G6,G7,G8,G9,G10,G11,G12,G14,G15,G16,G17,G18,G19,G20,G21,G22,G23,G24,G25,G26,G27,G28,G29,G30,G31,G32,G33,G34,G35,G36,G37,G38,G39,G40,G41,G42,G43,G44,G45,G46,G47,G48,G49,G50,G51,G52,G53,G54,G55,G56,G57,G58,G59,G60,G61,G62,G63,G64,G65,G66,G67"

<<<<but over here, my input cells got more than 66cells, when i try to
put G69, G71, G73.... after G67, it give me a run time error 1004-
method 'range' of object'-worksheet' failed
any way to solve this method?>>>>

======================================================================

Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("PartsData")

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

With inputWks
Set myRng = .Range(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

 

[Dave Peterson]

How about:

myCopy = "C2,G2:G12,G14:G67"

If I read that long string correctly.