CODEWITHSUNDEEP
excelvba
Sean Connecticut
Sean Connecticut

Reputation: 305

Organize columns by headers

okay the first instance of this question wasn´t perfectly explained so I figured I´d give it another shot. What I want to do is paste the column that contains the header sector in sheet1 into column A of sheet2. I have the following code but it keeps giving me the error "'range' of '_object global' failed" on the "range(strC).select" line which I´m assuming is because strC stores as the number of a column and not the letter but I´m not sure. Any ideas on how to solve this?

Sub CorrectOrder()   
Dim strC As String   
Dim cl As Range

strC = 0

For Each cl In Workbooks("Report.xls").Worksheets("Sheet1").Range("A1:AZ1")   
If cl.Value = "Sector" Then   
strC = cl.Column   
Workbooks("Report.xls").Worksheets("Sheet1").Range(strC).Select   
Selection.Copy   
Workbooks("Report.xls").Worksheets("Sheet2").Range ("A1")   
Selection.Paste   
End If   
Next cl   
End Sub

Upvotes: 0

Views: 99

Answers (1)

Alex P
Alex P

Reputation: 12497

I'd do it this way:

Sub CorrectOrder()
    Dim searchRange As Range, cl As Range

    Set searchRange = Workbooks("Report.xls").Worksheets("Sheet1").Range("A1:AZ1")

    For Each cl In searchRange
        If cl = "Sector" Then
            Workbooks("Report.xls").Worksheets("Sheet1").Columns(cl.Column).Copy Destination:=Workbooks("Report.xls").Worksheets("Sheet2").Range("A1")
        End If
    Next cl
End Sub

Basically use Columns to reference the column. And don't bother selecting when copying and pasting.

Upvotes: 1

Related Questions