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

Jeff62

New Member
Joined
Sep 7, 2014
Messages
2
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.


Customer IDCostGallonsRevenueDescriptionDate

<tbody>
</tbody><colgroup><col><col><col span="3"><col><col><col span="2"><col><col></colgroup>
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.
 
Upvote 0
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?
 
Upvote 0
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:
Code:
=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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top