0

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
Improve this question
3
  • You are looking at Range C2:C100, If it's not empty, copy the data to the same sheet, Column A? Commented Jan 13, 2015 at 12:23
  • The problem with Set PartListRange = Sheets("Query").Range("C2:100")1 is C2:C100 Commented Jan 13, 2015 at 12:31
  • copy the data one at a time only in to cell "A2" :) Commented Jan 13, 2015 at 12:37

2 Answers 2

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

How would I edit this so that each cell it copied, it was only pasted in to cell "A2"?
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")
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".
Revised to pause with MsgBox
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
|
0 
If Cel.Value <> "" Then

you spelt Cell wrong

ParListCell

and Part :P

Improve this answer

2 Comments

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")
Thank you for fixing my bad spelling ;-)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.