Copy paste column based on range from another column

[Aviles]

Hi,

I have the below code which looks up column headings from one sheet and pastes the columns into another sheet. It works well, apart from situations where one column has cells that are blank, in which case it only does a partial copy/paste because it stops copying when it reaches a blank cell.

In this example, the 'Date' column will never have any blank cells. Is there a way to have this code look up the range for the 'Date' column and base the copy/paste from that column range?

Thanks.

Dim vHeader As Variant, rngFound As Range, i As Long
    For Each vHeader In Array("Date", "Time", "C", "Country/Region", "Event", "S")
        Set rngFound = Sheets("BBGExport").Cells.Find(vHeader, , xlValues, xlWhole, 1, 1, 0)
        i = i + 1
        If Not rngFound Is Nothing Then
            Range(rngFound, rngFound.End(xlDown)).Copy Destination:=Sheets("Scheduler").Cells(1, i)
        End If
    Next

 

[footoo]

Try this :

Range(rngFound, Cells(Rows.Count, rngFound.Column).End(xlUp)).Copy Destination:=Sheets("Scheduler").Cells(1, i)

 

[Aviles]

Try this :

Range(rngFound, Cells(Rows.Count, rngFound.Column).End(xlUp)).Copy Destination:=Sheets("Scheduler").Cells(1, i)

Hi @footoo thanks for the quick reply, but I'm getting the following error:

Run-time error '1004':
Method 'Range' of object'_Global' failed

 

[Trebor76]

Hi Aviles,

See how this goes:

Option Explicit
Sub Macro1()

    Dim vHeader As Variant, rngFound As Range, i As Long, lngLastRow As Long
    Dim strCol As String
    
    Application.ScreenUpdating = False
    
    For Each vHeader In Array("Date", "Time", "C", "Country/Region", "Event", "S")
        Set rngFound = Sheets("BBGExport").Cells.Find(vHeader, , xlValues, xlWhole, 1, 1, 0)
        i = i + 1
        If Not rngFound Is Nothing Then
            strCol = Split(rngFound.Address, "$")(1) 'Though we don't need to use the column letter I find it much easier to work with
            lngLastRow = Sheets("BBGExport").Cells(Rows.Count, strCol).End(xlUp).Row
            'Range(rngFound, rngFound.End(xlDown)).Copy Destination:=Sheets("Scheduler").Cells(1, i)
            Sheets("BBGExport").Range(strCol & "1:" & strCol & lngLastRow).Copy Destination:=Sheets("Scheduler").Cells(1, i)
        End If
    Next vHeader
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[Trebor76]

Hi @footoo thanks for the quick reply, but I'm getting the following error:

Run-time error '1004':
Method 'Range' of object'_Global' failed

Change this line...

Range(rngFound, Cells(Rows.Count, rngFound.Column).End(xlUp)).Copy Destination:=Sheets("Scheduler").Cells(1, i)

...to this:

Range(rngFound, Sheets("BBGExport").Cells(Rows.Count, rngFound.Column).End(xlUp)).Copy Destination:=Sheets("Scheduler").Cells(1, i)