Copy specified columns into new sheet

[doriannjeshi]

Hi,
This macro wont copy the entire column if there are gaps in the column with no data, will stop copying the rest of the column.
Can it be fixed to copy the whole column ?

Sheets.Add After:=ActiveSheet
Dim vHeader As Variant, rngFound As Range, i As Long
For Each vHeader In Array("EAN/UPC", "demo", "demo", "demo", "demo demo", "demo", "demo", "demo", "demo demo", "demo demo demo", "demo demo", "demo demo", "demo demo")
Set rngFound = Sheets(1).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(2).Cells(1, i)
End If

 

[MARK858]

Try

Range(rngFound, Cells(Rows.count, rngFound.Column).End(xlUp)).Copy Sheets(2).Cells(1, i)

 

[doriannjeshi]

Try

Range(rngFound, Cells(Rows.count, rngFound.Column).End(xlUp)).Copy Sheets(2).Cells(1, i)

Thank you Mark
I mark your response as solution but it wont update

 

[doriannjeshi]

Try

Range(rngFound, Cells(Rows.count, rngFound.Column).End(xlUp)).Copy Sheets(2).Cells(1, i)

Dim vHeader As Variant, rngFound As Range, i As Long
For Each vHeader In Array("demo", "demo", "demo", "demo", "demo")
Set rngFound = Sheets(1).Cells.Find(vHeader, , xlValues, xlWhole, 1, 1, 0)
i = i + 1
If Not rngFound Is Nothing Then

Range(rngFound, Cells(Rows.Count, rngFound.Column).End(xlUp)).Copy Sheets(2).Cells(1, i)

 

[MARK858]

What is the name of the sheet the columns are being copied from?

 

[jolivanes]

End(xlDown) will stop at the first empty cell. It is normally better to work from the bottom up as Mark showed you.

Maybe explain what you want to do. Quite often showing code that does not work as intended can complicate matters because of wrong thoughts to start with.

 

[MARK858]

as Mark showed you.

I think he is running it with a different sheet active so the Cells need qualifying in the code I posted with the sheet or you get the error shown (I would prefer to use the name as I don't like using it's Index as in the original code)

 

[doriannjeshi]

I think he is running it with a different sheet active so the Cells need qualifying in the code I posted with the sheet or you get the error shown (I would prefer to use the name as I don't like using it's Index as in the original code)

the name is sheet1

 

[MARK858]

Range(rngFound, Sheets("Sheet1").Cells(Rows.count, rngFound.Column).End(xlUp)).Copy Sheets(2).Cells(1, i)

Obviously assumes that Sheets(1) is the same sheet as Sheets("Sheet1") as rngFound needs to be the same sheet