VBA / Excel 2007 - Moving columns from one sheet to another - not in order

[Jeff62]

Hello,

I am trying to move data from one worksheet to another worksheet. The columns I want to move are not in order. I copied one of you codes and made some minor changes.

Sub test()
Dim Headers As Variant
Dim ColumnNumbers As Variant
Dim i As Long
Dim SourceColumn As Range
Dim DestinationSheet As Worksheet

Set DestinationSheet = ThisWorkbook.Sheets("Sorted")
Headers = Array("Customer", "Tail Number", "Description", "Date", "Gallons", "Revenue", "Cost")
ColumnNumbers = Array(1, 2, "J", "K", "F", "G", "B", "C", "D")

For i = LBound(Headers) To UBound(Headers)
With ThisWorkbook.Sheets("FuelMarginTrendAnalysis").Rows(1)
Set SourceColumn = .Find(Headers(i), after:=.Cells(1, 1), MatchCase:=False)
End With
If Not SourceColumn Is Nothing Then
SourceColumn.EntireColumn.Copy Destination:=DestinationSheet.Columns(ColumnNumbers(i))
End If
Next i

End Sub


This is what I am getting. I appreciate the help.


[TABLE="width: 867"]
<tbody>[TR]
[TD]Customer ID[/TD]
[TD]Cost[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Gallons[/TD]
[TD]Revenue[/TD]
[TD][/TD]
[TD][/TD]
[TD]Description[/TD]
[TD]Date
[/TD]
[/TR]
</tbody><colgroup><col><col><col span="3"><col><col><col span="2"><col><col></colgroup>[/TABLE]

 

[JLGWhiz]

You have it overwriting Tail Number with Cost. In you Column Numbers Array, Column 2 and "B" are the same column, I suggest you use all numbers or all alpha characters for your array to avoid confusion. BTW, the ColumnNumbers array has two more elements than your Headers array. That might be the cause of the problem.

 

[Jeff62]

Thanks JLGWhiz

Works!

I am trying to put a formula in that would subtract "Revenue" and "Cost" and show on a new column "I". The problem is I don't want zero show after the data is doneand there are extra rows assigned due to the data file having more and some times less rows. Any help would be appreciated?

 

[JLGWhiz]

Thanks JLGWhiz

Works!

I am trying to put a formula in that would subtract "Revenue" and "Cost" and show on a new column "I". The problem is I don't want zero show after the data is doneand there are extra rows assigned due to the data file having more and some times less rows. Any help would be appreciated?

Assuming Cost is in Column B and Revenue is in Column G Then in cell I2:

=If(G2>B2, G2 - B2, "")

then drag that down column I as far as your data in the other columns go or as far as you think they might go. The cell will appear blank unless there is something in column G.