Excel VBA Macro: Pull Specific Columns from One Sheet to Another (Dynamically)

Excel VBA Macro: Pull Specific Columns from One Sheet to Another (Dynamically). In this video, we create a macro that automatically populates a worksheet with data from specific columns on another sheet from the same workbook. We choose the columns we wish to pull by typing the corresponding headers in our workbook, and using a Do While Loop nested in a For Loop to match off the headers and copy and paste the desired columns.

Code (YouTube doesn't allow brackets; so LT and GT are used for less than and greater than, respectively):

Sub pull_columns_over()

Dim head_count As Integer
Dim row_count As Integer
Dim col_count As Integer
Dim i As Integer
Dim j As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = ThisWorkbook.Sheets("Raw Data")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

ws2.Activate
head_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))

ws1.Activate
col_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))
row_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))

For i = 1 To head_count

j = 1

Do While j LT= col_count

If ws2.Cells(1, i) = ws1.Cells(1, j).Text Then

ws1.Range(Cells(1, j), Cells(row_count, j)).Copy
ws2.Cells(1, i).PasteSpecial xlPasteValues
Application.CutCopyMode = False
j = col_count

End If

j = j + 1

Loop

Next i

With ws2
.Activate
.Cells(1, 1).Select
End With

End Sub

#ExcelVBA #ExcelMacro

[spltorky]

You are a saint. Thank you so much for this. It worked perfectly for extracting specific columns out of sheets that have upwards of 700 columns (and I only need like 50 on average every time).
Bless you!

[Jojosmith]

Perfect. I follo'w your instruction carefully. Trying to type in from screen first & use your written code as refference. You are GENIUS. Thank you so much Greg for all your hard work, time to educate us. Your tutorials are always valuable & absolutely thumbs up both hands 👌👏

[Adam_K_W]

Nicely done. Don't forget to set your objects to nothing before you exit the sub.

[jasonjohnston]

You completely blew my mind! This is outstanding. Thank you so much!!

[patriciapetri]

I love it!! thank you for teaching us. you made it so simple and easy to understand and follow... great job!!

[drewgossage]

Really cool, straight to the point, great tutorial!

[josephraji]

Very well explained and it is working very much fine for me after researching so much i found nowhere except yours so i subscribed to your channel.

[airman-xxvg]

finally, got it!!! thanks. code works great 👍👍👍👍

[airman-xxvg]

it would be a big help if you'll share the excel file with that VBA code. 😅😅😅

[davidlinton]

Could you use an advanced filter instead?

[arditiljazi]

thank you for showing this sample. is there a way of tweaking it to show it can copy and paste the specific columns to another worksheet?

[andytawandamahdania]

It's a great tutorial 👍
But can you tell us how to pull specific column with some applied filter? Thx

[niakubheka]

Hi Gregg. Thank you for the vid, it was very helpful. I used the code for my sheets and it worked very well. Can you please assist me with how I can put additional data from a different sheet underneath the already transferred data (on the next empty line) of the worksheet. I need to consolidate from different sheets. Your assistance will be greatly appreciated.

[abrahamruiz]

Do you happen to have a way to pull columns into different sheets at once?

[cathy___m]

This is insightful. Thank you.
But can you please do it with more than 2 sheets? I am struggling with consolidating my 2nd sheet into the master sheet. I managed to do it for the 1st sheet using the method. I know we have to use LastRow to copy into the lastrow of the master sheet but I still can’t seem to get it right.

[airman-xxvg]

don't know what's wrong but I always end up on copying and pasting just the first two columns from raw data on sheet 2 after running the codes, what could be the problem?

[johnnastasi]

Excellent, Can this macro be modified to always pull x number of columns and place them in a sheet sequentially Sheet-001, sheet-002, etc?

[tchhctp]

Hi there, how do you deal with copying over a column that contains dates? Thanks

[zingfam]

Hi, I followed your code but somehow the code is only copy and paste one column. Any idea why ? I want to copy only 8 columns.

[Jojosmith]

Hi Greg, I have a large spreadsheet with >50, 000 records. Problem I am facing is there is a heading row (without data) between these records. To consolidate the db I need to remove these heading rows (contain no data, simply heading) on spreadsheets. It is very challenging as it is a huge database. Could you please kindly help to make a tutorial how to resolve this issue? Thanks a lot Greg