Write select columns to array & back to another spreadsheet
The code can be much simpler than you have shown. These two lines should do
what your code does...
Const Cols2Copy As String = "A:A,B:B,E:E,F:F,J:J,K:K,L:L"
Worksheets("Sheet1").Range(Cols2Copy).Copy Worksheets("Sheet2").Range("A1")
Notice the format that the columns are specified in.
--
Rick (MVP - Excel)
"petedacook" wrote in message
...
I figured if I copied the data I would have to write each column to the
destination before copying the next column. I figred if i write the
columns
to 1 or more arrays I could write the array all at once, then write all at
one.
Is that not correct?
Thanks, Joel.
Pete
"Joel" wrote:
Why are you puting the data into an aray
CopyColumns = Array("A", "B", "E", "F", "j", "k", "L")
FirstRow = 2
With Sheets("Sheet1")
For Each col In CopyColumns
LastRow = .Range(col & Rows.Count).End(xlUp).Row
.Range(col & FirstRow & ":" & col & LastRow).Copy
With Sheets("Sheet2")
LastRow = .Range(col & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
.Range("A" & NewRow).Paste
End With
Next col
End With
"petedacook" wrote:
Hello,
I am trying to take data from select columns in a spreadsheet adn write
it
to another spreadsheet. The ciolumns are not aligned in any order, and
the
number of rows is unknown from week to week.
For example, I need to take columns: A, B, E, F , j, k, L, copy to
array,
and the number of rows is unknown. I then to to spit each column back
into
another spreadsheet in a specified column.
I have been reading two books, but it seems they only give examples of
arrays with a known number of rows.
Any help is greatly appreciated. I have been playing with this for
days.
Pete
|