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

Can you clarify something for me then. Given your example Columns A, B, E,
F, J, K and L... where should each of these map to?

--
Rick (MVP - Excel)


"petedacook" wrote in message
...
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

OK...

these are the desitnation columns:
A BC D e f g h I J K L M N O P



the source columns A:P need to go as follows. The first letter set is the
source column, the second is the destination column, some of them are in
order respectively as follows:

D to E
G to F
I:K to G:I
M:O to J:L
S:W to M:Q

A:C in the source file contains bogus data. I wrote code to fill A:C with
the correct data. I copy and paste the file as a whole unit. I added a loop
because the user needs to import more than one file.

If you see an opportunity for improvement, please let me know. I am fairly
new to this.

Sub Get_SAP_Filename()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Dim Station As String * 3
Dim Invnum As Variant
Dim Unit As Integer
Dim Fromdate As String * 11
Dim Todate As String * 11
'Dim currentWB As Workbook
'Dim filenameb As Workbook



currentWB = ThisWorkbook.Name




Do Until answer = vbNo
answer = MsgBox("Do you have more SAP files to Import?", vbYesNo)
If answer = vbNo Then Exit Sub


Filt = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Files (*.csv),*.csv," & _
"Excel Files (*.XLS),*.XLS," & _
"All Files (*.*),*.*"

FilterIndex = 5


Title = "Select a File to Import"

' Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title)

' Exit if dialog box canceled
If FileName = False Then
MsgBox "No file was selected."
Exit Sub
End If

' Open the SAP File

Workbooks.Open FileName:=FileName, ReadOnly:=True



' get file name unit name, etc

filenameb = Right(FileName, 7)
Station = Left(filenameb, 3)

' Get unit number
If Station = "CMH" Then
Unit = "126"
Else
If Station = "EWR" Then
Unit = "119"
Else
If Station = "MEM" Then
Unit = "132"
Else
If Station = "STL" Then
Unit = "153"
Else
If Station = "IND" Then
Unit = "165"
Else
If Station = "SAT" Then
Unit = "153"
Else
If Station = "ORD" Then
Unit = "240"
Else
If Station = "HNL" Then
Unit = "244"
Else
If Station = "OGG" Then
Unit = "731"
Else
If Station = "SAN" Then
Unit = "734"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If


Workbooks(filenameb).Activate
Invnum = Cells(10, 1)
Columns("E:F").Delete shift:=(xlToLeft)
Columns("F").Delete shift:=(xlToLeft)
Columns("I").Delete shift:=(xlToLeft)
Columns("L:N").Delete shift:=(xlToLeft)
Rows(1).Delete shift:=(xlUp)

' Edit Column A so it is now the unit name
Cells(1, 1).Select

Do While ActiveCell.Value < ""
ActiveCell.Value = Station
ActiveCell.Offset(1, 0).Select
Loop

' Change Miscellaneous to C&I
Cells(1, 8).Select

Do While ActiveCell.Value < ""
If ActiveCell.Value = "Miscellaneous" Then ActiveCell.Value = "C&I"
ActiveCell.Offset(1, 0).Select
Loop

' Populate the Unit #
Cells(1, 2).Select

Do While ActiveCell.Value < ""
ActiveCell.Value = Unit
ActiveCell.Offset(1, 0).Select

Loop

Columns("C").Insert shift:=xlToRight

' Populate FromDate

Cells(1, 3).Select
Fromdate = Mid(Cells(1, 6), 5, 2) & "/" & Right(Cells(1, 6), 2) & "/" &
Left(Cells(1, 6), 4)

Do While ActiveCell.Offset(0, -1).Value < ""
ActiveCell.Value = Fromdate
ActiveCell.Offset(1, 0).Select

Loop


' populate the ToDate
Cells(1, 4).Select
Todate = Mid(Cells(1, 6).End(xlDown), 5, 2) & "/" & Right(Cells(1,
6).End(xlDown), 2) & "/" & Left(Cells(1, 6).End(xlDown), 4)
Do While ActiveCell.Offset(0, -1) < ""
ActiveCell.Value = Todate
ActiveCell.Offset(1, 0).Select
Loop


Range("A1").CurrentRegion.Copy
Workbooks(currentWB).Sheets("Detail").Range("A5000 0").End(xlUp)
Workbooks(filenameb).Close
Sheets("Invoice").Select

If Station = "CMH" Then
Cells(25, 2).Value = "CMH"
Cells(25, 1).Value = Invnum
Else
If Station = "EWR" Then
Cells(24, 2).Value = "EWR"
Cells(24, 1).Value = Invnum
Else
If Station = "MEM" Then
Cells(26, 2).Value = "MEM"
Cells(26, 1).Value = Invnum
Else
If Station = "STL" Then
Cells(27, 2).Value = "STL"
Cells(27, 1).Value = Invnum
Else
If Station = "IND" Then
Cells(28, 2).Value = "IND"
Cells(28, 1).Value = Invnum
Else
If Station = "SAT" Then
Cells(29, 2).Value = "SAT"
Cells(29, 1).Value = Invnum
Else
If Station = "ORD" Then
Cells(30, 2).Value = "ORD"
Cells(30, 1).Value = Invnum
Else
If Station = "HNL" Then
Cells(31, 2).Value = "HNL"
Cells(31, 1).Value = Invnum
Else
If Station = "OGG" Then
Cells(32, 2).Value = "OGG"
Cells(32, 1).Value = Invnum
Else
If Station = "SAN" Then
Cells(33, 2).Value = "SAN"
Cells(33, 1).Value = Invnum
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

'Clear Station
'Clear Unit
'Delete Invnum
'Delete FileName
'Delete filenameb

Loop

Sheets("invoice").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic









"Rick Rothstein" wrote:

Can you clarify something for me then. Given your example Columns A, B, E,
F, J, K and L... where should each of these map to?

--
Rick (MVP - Excel)


"petedacook" wrote in message
...
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

And thanks Rick and Joel!



"Rick Rothstein" wrote:

Can you clarify something for me then. Given your example Columns A, B, E,
F, J, K and L... where should each of these map to?

--
Rick (MVP - Excel)


"petedacook" wrote in message
...
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