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)

 

[Aviles]

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)

Thanks @Trebor76 that worked great!

If you don't mind, for my knowledge, can you tell me which part of this code picks up the correct range?

 

[Trebor76]

Thanks @Trebor76 that worked great!

That's great and you're welcome although footoo should really be thanked

This picks up the range:

Range(rngFound, Sheets("BBGExport").Cells(Rows.Count, rngFound.Column).End(xlUp))

The only issue with the original code was that it must have been run on a different tab than where the source data resides (i.e. BBGExport) so prefixing the range with it made the code run as expected.

Thanks,

Robert

 

[Aviles]

That's great and you're welcome although footoo should really be thanked

This picks up the range:

Range(rngFound, Sheets("BBGExport").Cells(Rows.Count, rngFound.Column).End(xlUp))

The only issue with the original code was that it must have been run on a different tab than where the source data resides (i.e. BBGExport) so prefixing the range with it made the code run as expected.

Thanks,

Robert

Thanks for clarifying, and yes, thank you @footoo also