VBA in Excel 2003 - copy and paste then clear

March 1, 2011 at 11:35:50
Specs: Windows XP
I am extremely new to VBA. I have a basic program where a person will enter details into worksheet "Survey". Once done entering the information, they click the button "Add to Database". This will execute a macro that copies the different cells from the "Survey" sheet and places them in the the "Database" sheet on the next available row. Then the cells on the "Survey" sheet are cleared out.

The cells that are copied are not continuous. There are 156 cells of information of varying sizes and types that need to be copied.

Once clicking on the button, I receive an error that says:
Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

When I debug the code, it highlights the line "Set myRng = .Range(myCopy)" as wrong

I believe the "String" is too long so the "Range" cannot be made. Please help...

Option Explicit

Sub UpdateLogWorksheet()

Dim Database As Worksheet
Dim Survey 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 = "C4,C6,C8,C10,C12,C14,C16,C19,C20,C21,C22,C23,C24,C25,C26,C27,C28,D28,C29,D29,C30,C31,C32,C33,C34,C35,C36,C37,C38,C39,D39,C42,C43,C44,C45,C46,C47,C48,C49,C50,C51,C52,C53,C54,C55,C56,C57,C58,C59,C60,C61,C62,D62,C63,C65,C68,C69,C70,C73,C75,C76,C77,C78,C80,C81,C82,C83,C84,C85,C86,C87,C88,C89,C91,C92,C93,C94,C95,C96,C97,C99,D99,C102,C103,C104,C105,C108,C109,C110,C111,C112,C113,C114,C115,C116,D116,C119,C120,C121,C122,C123,C124,C125,C126,C127,C128,C129,C130,C131,C132,C133,C134,C135,C136,D136,C139,C140,C141,C142,C143,C144,C145,C146,C147,C148,C149,C150,C151,C152,C153,C154,C155,D155,C158,C159,C160,C161,C162,C163,D163,C164,C165,D165,C166,C167,C168,C169,C170,C171,C174,C175,C176,C177,C178,C179,C180,D180"

Set Survey = Worksheets("Survey")
Set Database = Worksheets("Database")

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

With Survey
Set myRng = .Range(myCopy)
End With

With Database
.Cells(nextRow, "B").Value = Application.UserName
oCol = 1
For Each myCell In myRng.Cells
Database.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With

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

See More: VBA in Excel 2003 - copy and paste then clear

Report •

March 2, 2011 at 03:05:56
Have you tried just making a named range, and referring to that in your VBA?

How To Ask Questions The Smart Way

Report •

March 2, 2011 at 05:46:50

Again, I am really very new to this. I thought I was doing that by making the String "myCopy" and then sending it into the named Range "myRng". But of course I can't create myRng without it bombing out. I guess I don't know the proper syntax to do this.


Report •

Related Solutions

Ask Question