Move Column by Header

[Jvalades]

Hello Everyone,

The issue I am working on today is being able to use the .find function to find a column header and copy all the data to be pasted into another worksheet.

'Find Customer Sub

Cells.find(what:="customerSub", after:=Range("A1"), lookin:=xlformulas, lookat:= xlpart _
, Searchorder:= xlbyrows.entirecolumn.copy

Sheets("DI").select
Range("A4").select
Activesheet.paste
Application.cutcopymode = false

so when I try and paste this information it does not fit the row count of the "DI" worksheet.

Any thoughts?
Cheers!

 

[NoSparks]

The entire column 1,048,576 rows, do you really need to copy them all ?
Anyway, try this copy

Cells.Find(what:="customerSub", after:=Range("A1"), LookIn:=xlFormulas, lookat:=xlPart, _
           Searchorder:=xlByRows).EntireColumn.Resize(Rows.Count - 4).Copy

 

[Jvalades]

NoSparks,

I will try this today. To answer your question, no, I do not need all the rows. But I am unfamiliar with VBA and did not know how to add parameters. (.resize(rows.count - 4)

Can you explain if I am interpreting the code correctly. You are counting the total rows and just subtracting the last 4, then copying these rows?

Thanks again,
J

 

[NoSparks]

Yes, you are interpreting the code correctly, but it's not the way to do what you're wanting.

A google search for "excel vba last used row" returns this Ron de Bruin page as the first item.

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

is the most common way to obtain the last row with data in a given column, and

Have a look at this Microsoft page to see how to use .find for finding the particular header you were looking for.


this is one way to do what you're wanting

Sub Testing()

    Dim lr As Long          'the last row
    Dim col As Long         'the column
    Dim fndCust As Range    'the cell where found

'locate CustomerSub on the active sheet
With ActiveSheet

    Set fndCust = .Cells.Find(what:="customerSub", after:=Range("A1"), _
                              LookIn:=xlFormulas, lookat:=xlPart, _
                              Searchorder:=xlByRows)
    
    If Not fndCust Is Nothing Then
        'the column customerSub was found in
        col = fndCust.Column
        'the last used row in that column
        lr = .Cells(Rows.Count, col).End(xlUp).Row
        'copy the range
        .Range(fndCust, .Cells(lr, col)).Copy Sheets("DI").Range("A4")
    End If

End With

Application.CutCopyMode = False

End Sub

Hope this is of some help, remember..... Google is your friend.