You have the answer for Union (won't work as you want).

If you data is set up like a database where you have column headers in row1,
you could do this with the Advanced Filter
Sub ABC()
With Worksheets("Sheet1")
Worksheets("Sheet2").Range("A1:C1") = Array(.Range("B1").Value, _
.Range("C1").Value, .Range("A1").Value)
End With
Sheets("Sheet1").Range("A1:C100").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Worksheets("Sheet2").Range("A1:C1"), _
Unique:=False


End Sub

The disadvantage or advantage depending on your desires is that this copies
everything as a value (formulas are lost - results are copied).

--
Regards,
Tom Ogilvy




"Noah" wrote in message
...
Is it possible to use the Union method (or some other method) to join
together multiple ranges in an order that is different original order of
columns on the worksheet? For example, I would like rng4 in the macro
below
to have column B on the left, column C in the middle, and column A on the
right. Thanks!

Sub macro1()
Sheets("Sheet1").Select
Set rng1 = Range("A1:A100")
Set rng2 = Range("B1:B100")
Set rng3 = Range("C1:C100")
Set rng4 = Application.Union(rng2, rng3, rng1)
rng4.Copy Sheets("Sheet2").Range("A1")
End Sub