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