Copy and Paste columns based on headers matching a list

[mjayz]

Hi

I have sheet1 with approx 60 columns with unique headers in row 1. I want to copy some of these columns into a new sheet based on a list in sheet2, column A. So if there are 3 rows in that list, it will copy & paste 3 columns into the new sheet being columns A,B & C.

My knowledge of using variables and ranges is not good so I appreciate the help.

Thanks
MJ

 

[Tim_Excel_]

With ThisWorkbook
LRow = .Sheets(2).Cells(.Sheets(2).Rows.Count, "A").End(xlUp).Row

.Sheets(3).Columns(1).Resize(, LRow).Value = .Sheets(1).Columns(1).Resize(, LRow).Value
End With

This copies the only the values from one sheet to the other

 

[mjayz]

Thanks for the quick response Tim.

Apologies I was a bit vague on the request. The list in sheet2 should match the headers in sheet1 and copy those columns only. Not necessarily the first 3 columns but it could be column D, J, & K if those are the matching headers which then go into column A,B & C in sheet3 in that order. The list in sheet2 will vary depending on what I need for the report. Could contain 3 headers up to 20 probably.

Thanks
MJ

 

[Tim_Excel_]

With ThisWorkbook
LRow = .Sheets(2).Cells(.Sheets(2).Rows.Count, "A").End(xlUp).Row

For each cell in .Sheets(2).Range("A1:A" & LRow)
    Set FindHeader = .Sheets(1).Rows(1).Find(cell.value)
    If not FindHeader Is Nothing Then
        LastCol = .Sheets(3).Cells(1, .Sheets(3).Columns.Count).End(xlToLeft).Column + 1
        .Sheets(3).Columns(LastCol).Value = .Sheets(1).Columns(FindHeader.Column).Value
    End If
Next cell