Excel VBA Iteration Copy and Paste

Question

I'm trying to create a macro that declares a range of cells and copies and pastes each cell 1 at a time in to cell "A2". This should only happen if the cell in column have data present.

The error I'm getting is a Run-Time error '1004' Application-defined or object-defined error.

I'm more from a C# background so I'm hoping its just a syntax error but this is what I have so far.

Any help will be greatly appreciated.

Sub Update1()
'
' Update1 Macro
' Update
'
  Dim PartListRange As Range
  Dim PartListCell As Range

  Set PartListRange = Sheets("Query").Range("C2:100")

  For Each PartListCell In PartListRange.Cells
     If Cell.Value <> "" Then
         Worksheets("Query").Range(PartListCell).Copy _
         Destination:=Worksheets("Query").Range("A2")
         ActiveWorkbook.RefreshAll
     End If
  Next PartListCell

End Sub

Answer 1

This is a slightly different way of doing the same thing, using .Cells(row,col) and setting the values using a loop.

Using lastRow, instead of row 100. You can change that if need be. I'm including it, because most times that's needed.

What this does:

• Loop through the range of 2-100 or 2-lastRow, depending on your choice.

• Check the value in column "C" to make sure it isn't empty.

• If not empty, copy to Range("A2"). (note, each iteration that the conditions match will over-write)

• Pause in the action to run a macro or call another sub, using a MsgBox, in this example as a placeholder

Code: Un-comment lines related to lastRow to utilize it.

Sub conditionalCopy()

'Dim lastRow As Long
Dim lRow As Long
Dim sName As String

    sName = "Query"
    'lastRow = Sheets(sName).Range("C" & Rows.count).End(xlUp).row

    'For lRow = 2 to lastRow        'Uncomment lastRow to use instead of 100 for max.
    For lRow = 2 To 100             'Range("C2:C100")    
        If Sheets(sName).Cells(lRow, "C").Text <> "" Then
            Sheets(sName).Range("A2").Value = Sheets(sName).Cells(lRow, "C").Value
            MsgBox("Here is your break in the action..." & vbNewLine & _
                   "Press Enter to continue loop")
        End If
    Next lRow

End Sub

Answer 2

If Cel.Value <> "" Then

you spelt Cell wrong

ParListCell

and Part :P