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

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

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

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

Rick: Your code won't put the results into one column. Your results will end
up in multiple columns

Pete: It is not easy to append one aray onto another array. You will need a
loop. It is more efficent to just copy from worksheet to workwsheet instead
of going through an intermediate aray.

"Rick Rothstein" wrote:

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

Ah, yes, I see I missed the "in a specified column" part of the OP's
original post.

--
Rick (MVP - Excel)


"Joel" wrote in message
...
Rick: Your code won't put the results into one column. Your results will
end
up in multiple columns

Pete: It is not easy to append one aray onto another array. You will need
a
loop. It is more efficent to just copy from worksheet to workwsheet
instead
of going through an intermediate aray.

"Rick Rothstein" wrote:

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

Joel,

I think you answered my question. I thought it would be faster to write the
data to an array, then spit it out. Rather than copy/paste one column at a
time.

I think there may be a misunderstanding and I apologize for not being more
clrea. The column that is copied may not go into the corresponding column on
the other sheet. Column A data may go in column B on the destination sheet.

I will work on the copy paste method.

Thanks again,

Pete









"Joel" wrote:

Rick: Your code won't put the results into one column. Your results will end
up in multiple columns

Pete: It is not easy to append one aray onto another array. You will need a
loop. It is more efficent to just copy from worksheet to workwsheet instead
of going through an intermediate aray.

"Rick Rothstein" wrote:

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