Copying ranges from one sheet to another

[Sinobato]

Hi All,

I have earlier posted this question but I think my earlier message wa
not cler, so I am re-posting this again.

I need some help with a problem regarding copying of a range from on
worksheet to another. Although I already have a code, I am getting som
errors, of which I want to ask advise from anyone, as I am not a VB/VB
programmer.

Here is what I would like to do: I have two worksheets, Sheet1 an
Sheet2, with Sheet1 containing the data that will be copied and sheet
as the target sheet.

My Sheet1 looks something like this:

A B C D E F
1 1 1 2 2 3 3
2 1 1 2 2 3 3
3 1 1 2 2 3 3
4 1 1 2 2 3 3
5 1 1 2 2 3 3

And my Sheet2 will originally look like this:

A B C D E F
1 3
2 5
3 1
4
5

When I execute my script, Sheet2 will look something like this:

A B C D E F G
1 3 2 2 3 3 1 1
2 5 2 2 3 3 1 1
3 1 2 2 3 3 1 1
4 * 2 2 3 3 1 1
5 * 2 2 3 3 1 1

* signifies space for presentation purposes

Here is my code:

Code
-------------------

Public Sub CopyThere()

Dim tmpColCtr, targetCol As Integer
Dim rrow As Range
Dim tempSheet, mainSheet As String

tempSheet = "Sheet2"
mainSheet = "Sheet1"
tmpColCtr = 3

With Worksheets(tempSheet)
For Each rrow In .Range("A:A")
If rrow.Value <> "" Then
targetCol = rrow.Value
MsgBox "Column value in row is " & targetCol
Sheets(mainSheet).Range(Cells(1, targetCol), Cells(5, (targetCol + 1))).Copy _
Destination:=Sheets(tempSheet).Range(Cells(1, tmpColCtr))
tmpColCtr = tmpColCtr + 2

Else
Exit For
End If

Next rrow
End With

MsgBox "Copying of data completed!"

End Sub

-------------------


When I execute this code, using the worksheet data I have above, I a
getting the error "Run-time error '1004': Application-defined o
object-defined error".

Am I missing something on my declarations, or using th
methods/properties incorrectly? Please help!

Thanks in advance for your help!
Sinobat

 

[K Dales]

I think the problem comes from this line:
For each rrow in .Range("A:A")

rrow is a Range object. You are treating it as a Cell.
It is true that a single cell is a range, but not vice-
versa. So you are not, as you seem to think, stepping
through each cell in the column but each Range in the
column. To clarify: all of the below are ranges within
column A:
A1; A1:A2; A1:A3; A2:A3; A2:A65536

So when you say something like "rrow.Value" what does it
mean?

As a side point, even if it worked (i.e. rrow gave you the
value of each cell in column A) the code would be very
inefficient and slow, since to copy your few rows of data
it would have to loop through each of the 65536 cells in
the column!

Here's an alternative way to find and step through
the "filled in" rows in your table:

Dim UsedRows as Long, RowStep as Long
....

With Worksheets(tempSheet).Range("A1")
UsedRows = .CurrentRegion.Rows.Count
For RowStep = 1 to UsedRows
targetCol = .Offset(RowStep-1, 0).Value
...
Next RowStep
....
End With