CODEWITHSUNDEEP
vbaexcel
Anirudh Chauhan
Anirudh Chauhan

Reputation: 111

Selecting a column in excel but from the desired position

I have two workbooks and what I want is to copy a column from one excel to another. In the column, I want to select the data from below the header till the bottom Since, my header USER is somewhere at B14 , so I am using .Find(What:="User") to find the header and storing it into aCell.

But when I am copying it into another excel, it is also selecting B1 to B13 which is not required.

What I only want is to select data from header till bottom (B14 to B80) and pasting it into another excel as A1 to A66.

Dim x As Workbook

Dim y As Workbook

Dim ws As Worksheet

Set x = Workbooks.Open("C:\file1.xls")

Dim aCell As Range


Set aCell = x.Sheets("file").Range("B1:B1000").Find(What:="User", LookIn:=xlValues,LookAt:=xlWhole, _
                                          MatchCase:=False, SearchFormat:=False)


Set y = Workbooks.Open("C:\file2.xls)

aCell.EntireColumn.Copy

y.Sheets("abc").Range("A1:A1000").PasteSpecial

End Sub

Upvotes: 1

Views: 48

Answers (1)

YowE3K
YowE3K

Reputation: 23974

If you don't want to copy the EntireColumn, just copy the range that you are interested in:

Dim x As Workbook
Dim y As Workbook
Dim ws As Worksheet
Dim aCell As Range

Set x = Workbooks.Open("C:\file1.xls")
With x.Sheets("file")
    Set aCell = .Range("B1:B1000").Find(What:="User", _
                                        LookIn:=xlValues, _
                                        LookAt:=xlWhole, _
                                        MatchCase:=False, _
                                        SearchFormat:=False)

    'Check that header exists
    If aCell Is Nothing Then Exit Sub

    Set y = Workbooks.Open("C:\file2.xls")
    .Range(aCell, .Cells(.Rows.Count, aCell.Column).End(xlUp)).Copy _
                                                y.Sheets("abc").Range("A1")
End With

Upvotes: 2

Related Questions