Copy subset of Range
Hi Simon,
Try the following example. Note that a space an underscore at the end of a
line is a line break in an otherwise single line of code. Explanation of what
the code is doing at bottom o this post.
Sub test()
Dim NewGLDData As Range
With Sheets("Sheet1")
Set NewGLDData = .Range("C1:K30")
End With
With NewGLDData
.Columns(2).Offset(1, 0) _
.Resize(.Rows.Count - 1, 1).Copy _
Destination:= _
Sheets("Sheet2").Range("A2")
End With
End Sub
The following is actually one line of code to copy and paste without
selecting.
..Columns(2).Offset(1, 0) _
.Resize(.Rows.Count - 1, 1).Copy _
Destination:= _
Sheets("Sheet2").Range("A2")
..Columns(2) is the second column of range NewGLDData.
..Offset(1, 0) shifts range down one row off headers but that then includes
an extra row at the bottom.
..Resize(.Rows.Count - 1, 1) reduces total rows by 1 to remove extra row at
bottom
Remainder of Copy Destination should be self explanatory.
--
Regards,
OssieMac
"Simon" wrote:
Hi,
I have a range object which covers multiple columns and multiple rows. The
first row of the range object contains Header information so its just
basically text, but I want to be able to copy the data underneath the
headers. My Range object is called NewGLData and I want to copy everything
in the second column excluding the first row? How can I do this?
I tried using something like this:
NewGLData(Columns(2)).Select
Selection.Copy
But I kept receiving a Type Mismatch error, if you have any advice as to how
I can overcome this, that would be great.
Thanks.
Simon
|