Range questions - Paste in order and Autofit

[Sayth]

Can I ask 2 quick questions.

I selected a range of columns and copied. When they paste into the new sheet however they don't paste in the order they were selected they paste in order of occurrence. here is my code. How can i paste as selected.

Dim WS As Worksheet
Set WS = Sheets.Add

    WS.Name = ("Report")
    ActiveCell.FormulaR1C1 = "=Table_owssvr_1[Column1]"
    Range("A1").Select
    Selection.ClearContents
    Sheets("Data").Select
    Range("O:O,P:P,S:S,R:R,E:E,F:F,G:G").Select
    Selection.Copy
    Sheets("Report").Select
    Range("A1").Select
    ActiveSheet.Paste

Second I defined a range of columns I wanted to apply autofit to but it errors. Is there a correct way to do this? this is what I did.

Columns("O:O,P:P,S:S,R:R,E:E,F:F,G:G").EntireColumn.AutoFit

 

[Sayth]

Oops second one solved.

Columns("A:G").EntireColumn.AutoFit

 

[ashish koul]

try this see if it helps

Sub tests()
Dim WS As Worksheet
Dim i As Long, rng As Range
Set WS = Sheets.Add
WS.Name = ("Report")
Set rng = Sheets("Data").Range("O:O,P:P,S:S,R:R,E:E,F:F,G:G")
    For i = 1 To rng.Areas.Count
        Sheets("data").Range(rng.Areas(i).Address).Copy Destination:=WS.Range("a1").Offset(0, i - 1)
    Next
WS.UsedRange.EntireColumn.AutoFit
End Sub

 

[Sayth]

try this see if it helps

Sub tests()
Dim WS As Worksheet
Dim i As Long, rng As Range
Set WS = Sheets.Add
WS.Name = ("Report")
Set rng = Sheets("Data").Range("O:O,P:P,S:S,R:R,E:E,F:F,G:G")
    For i = 1 To rng.Areas.Count
        Sheets("data").Range(rng.Areas(i).Address).Copy Destination:=WS.Range("a1").Offset(0, i - 1)
    Next
WS.UsedRange.EntireColumn.AutoFit
End Sub

Is there anywhere in there I can define pasteSpecial values only as it does solve the column problem but results in #ref values.

 

[ashish koul]

try this

Sub tests()
Dim WS As Worksheet
Dim i As Long, rng As Range
Set WS = Sheets.Add
WS.Name = ("Report")
Set rng = Sheets("Data").Range("O:O,P:P,S:S,R:R,E:E,F:F,G:G")
    For i = 1 To rng.Areas.Count
        
        Sheets("data").Range(rng.Areas(i).Address).Copy
        WS.Range("a1").Offset(0, i - 1).PasteSpecial Paste:=xlPasteValues
        
    Next
WS.UsedRange.EntireColumn.AutoFit
End Sub