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

You are looking at Range C2:C100, If it's not empty, copy the data to the same sheet, Column A? — peege
– peege, Commented Jan 13, 2015 at 12:23
The problem with Set PartListRange = Sheets("Query").Range("C2:100")1 is C2:C100 — peege
– peege, Commented Jan 13, 2015 at 12:31

peege · Accepted Answer · 2015-01-13 13:05:44Z

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

edited Jan 13, 2015 at 13:05

answered Jan 13, 2015 at 12:29

peege

2,4771 gold badge13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

demonLaMagra Over a year ago

How would I edit this so that each cell it copied, it was only pasted in to cell "A2"?

peege Over a year ago

Do you mean A2 for every row? You would only see the last one done. Because they'd all rewrite it. If you wanted to do that, you'd say .Cells(2,"A") instead of (lRow, "A")

demonLaMagra Over a year ago

Yeah that's what I want it do. Eventually I will call another macro after each iteration to gather data based on the data in Cell "A2".

peege Over a year ago

Revised to pause with MsgBox

demonLaMagra Over a year ago

how do I change this line of code to work with columns and not rows?? lastRow = Sheets(sName).Range("C" & Rows.count).End(xlUp).row

|

Box · Accepted Answer · 2015-01-13 12:26:10Z

0

If Cel.Value <> "" Then

you spelt Cell wrong

ParListCell

and Part :P

answered Jan 13, 2015 at 12:26

Box

878 bronze badges

2 Comments

demonLaMagra Over a year ago

This doesn't fix the error. Excel is saying the error is something to do with the line Set PartListRange = Sheets("Query").Range("C2:100")

demonLaMagra Over a year ago

Thank you for fixing my bad spelling ;-)

Collectives™ on Stack Overflow

Excel VBA Iteration Copy and Paste

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related