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

--
Message posted from http://www.ExcelForum.com