Copying ranges from one sheet to another
 

Hi all!

I am doing a script wherein I have two sheets, Sheet1 and Sheet2
Sheet1 contains my data and I am using Sheet2 as a temporary worksheet
What I want to do is copy a range of data from Sheet1 to a column i
Sheet2. Total number of ranges to be copied is dependent on the numbe
of data in Sheet2's column B (assume to be correct).

My ranges are composed of data from 2 columns and the rows starts fro
5 to 47. As an example, the first range of data that I want to cop
from Sheet1 is D5:E47 to Sheet2 C1, then Sheet1 F5:G47 to Sheet2 D1
Sheet1 H5:I47 to Sheet2 E1, and so on.

I have already my script here but when I try to run this but I a
getting error "Run-time error '1004': Application-defined o
object-defined error" on the line where I am already doing the copyin
(inside For loop). Can someone please review my code and help me wit
this?


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

Public Sub subCopySorted()
Dim tmpColCtr As Long
Dim rrow As Range
Dim tempSheet, mainSheet As String

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

' Column B on Sheet2 contains column numbers from Sheet1 which was sorted earlier on
' another script. Assume that the data on this column is correct.

For Each rrow In Sheets(tempSheet).Range("B:B")
If rrow.Value < "" Then

Sheets(mainSheet).Range(Cells(5, rrow.Value), Cells(47, rrow.Value + 1)).Copy _
Destination:=Sheets(tempSheet).Range(Cells(1, tmpColCtr))
tmpColCtr = tmpColCtr + 2

Else
Exit For
End If

Next

MsgBox "Copying of sorted data completed!"

End Sub

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


Thanks in advance for your help!
Sinobato:

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