Pull Specific Columns from Another Workbook (Based on Header Values) Dynamically | Excel VBA Macro

Excel VBA Macro: Pull Specific Columns from Another Workbook (Based on Header Values) Dynamically. In this video, we create a macro that automatically populates the current workbook with data from specific columns in another workbook from a specified location. 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()

Dim head_count As Integer
Dim row_count As Integer
Dim col_count As Integer
Dim i As Integer
Dim j As Integer
Dim ws As Worksheet

Application.ScreenUpdating = False

Set ws = ThisWorkbook.Sheets("Sheet1")

'count headers in this workbook
head_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))

'open other workbook and count rows and columns
ActiveWorkbook.Sheets(1).Activate

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

For i = 1 To head_count

j = 1

Do While j LT = col_count

If ws.Cells(1, i) = ActiveSheet.Cells(1, j).Text Then

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

End If

j = j + 1

Loop

Next i

ActiveWorkbook.Close savechanges:=False

ws.Cells(1, 1).Select

Application.ScreenUpdating = True

End Sub

#ExcelVBA #ExcelMacro

[amalhafiane]

Thank you so much!! You literally saved my internship

[siddharthmudaliar]

I really cannot thank you much for a brilliant tutorial there. I was trying to work on a piece and was just not able to wrap my head around it. This video solved everything and the code looks so decent and clean.
Fantastic job there mate.
*Subscribed the channel 🎉🎉
Keep up the good work

[judithvalentine]

Hi Greg, Thank you very much for your videos, it's really helping me.

[timtom]

The most helpful vba tutorial I've ever watched! Thank you so much!

I had to modify a few lines since activating the source sheet didn't work for me, the row_count and col_count gave me values based on the worksheet I was importing the data to. So all the places that assumed using the active sheet, i added "ws_source." in front of like this:

csv_path =
Set wb_source =
Set ws_source = wb_source.ActiveSheet
Debug.Print "csv_path =" & csv_path
row_count = WorksheetFunction.CountA(ws_source.Range("A1",
col_count = WorksheetFunction.CountA(ws_source.Range("A1",
Debug.Print "row_count = " & row_count
Debug.Print "col_count = " & col_count

[umamakeshvasanth]

Thank you man. Is there a way to pull columns from multiple sheets than just 1

[RameshAllur-fw]

This code is really great and simple Greg thanks

[maxwurgler]

Hi Greg, this is fantastic, thank you very much this is a great help and very well explained

[justinsingh]

Hello Greg, I cannot thank you enough for this incredible tutorial ! You crushed it.
The question I have after watching, and following along, is what needs to happen if you header row is not starting at A1 in the data workbook? "Thisworkbook" header is located at A1 but "Activeworkbook" is starting on row A5.

[andrewliu]

Hi Greg. This is very helpful. I'm creating this macro for other users to use so I've inserted a 2nd tab with comments and a macro button. Once I added a new tab, the macro does not work and the debug is highlighting the 'ws.Cells(1, 1).Select' line which I'm assuming could be because this works if there was only one tab in the workbook. How could I rewrite this if there was more than one tab?

[naomifagan]

How do you pull columns in across multiple workbooks ??

[alexrosen]

A very useful tutorial explained in an excellent way

[namith]

Hey Greg.. can you help with code for multiple worksheet.

[tknaved]

very much thankful to u for this video...

[Sk]

its wonderfull, short vba code for excellent report generation

[connorloze]

Hey Greg - this has been super helpful. One question I have is do you know a way to set criteria where the data I am pulling into the new workbook matches a specific date?

[hendrixchola]

Thanks for the video.the challenge I have is that the macro is not copying and pasting the last four records. Please help

[blakehorner]

Lots of good videos I've already seen and they've really helped me! So thank you!

I'm currently investigating the ability of one excel document to pull information from another document. Two questions I have so far in this:

1. Is it possible to make the file path adaptable? Where the document name can be changed but the column locations and everything are in the same location in every version of the document?

2. Does all of this work whether you are in a normal file explorer or Microsoft Teams?

I apologize, I've been working in Excel VBA for about 6 months now, but I have so much more to go, so I imagine this will not be the last time you will see me in your comments haha. I have managed to do a number of things with what I've learned, including changing a duplicate detection and deletion code to what I needed it to do, but I can't say for sure if it is the most efficient method possible.

Thanks!

[yeiayel]

mine can’t paste the values, it only closes the destination workbook 😅

[pankajpathania]

Thanks for this.
How to add to the first available row in the same code, if you can please reply.
Thanks

[mowgli]

Hi Greg. This is amazing! Thank you for sharing. I'm trying to do a reorder of 70+ columns within a table. And then potentially delete some columns that I do not need.
Background: I have 1 master report that is used for multiple teams and some teams want it cut down to a smaller amount of columns. So what I'm hoping to do is create a bunch of macros (buttons) to allow the master report that has been pasted to "worksheet2'" to modify based on the button choice.

How can I create a cleaner version of reordering columns within a table?

' Daily_Shortlist Macro
'

'
Range("Customize_Shortlist[[#Headers], [Recruiter Name]]").Select
Selection.Cut
Range("Customize_Shortlist[[#All], [Super Sector]]").Select
Selection.Insert Shift:=xlToRight
Range("Customize_Shortlist[[#Headers], [Hiring Manager Name]]").Select
Selection.Cut
Range("Customize_Shortlist[[#All], [Super Sector]]").Select
Selection.Insert Shift:=xlToRight
Range("Customize_Shortlist[[#Headers], [Posting Status]]").Select
Selection.Cut
Range("Customize_Shortlist[[#All], [Super Sector]]").Select
Selection.Insert Shift:=xlToRight
Range("Customize_Shortlist[[#Headers], [Status]]").Select
Selection.Cut
Range("Customize_Shortlist[[#All], [Super Sector]]").Select
Selection.Insert Shift:=xlToRight
Range("Customize_Shortlist[[#Headers], [Senior Leader]]").Select




Range("Customize_Shortlist[[#Headers], [Job Title(2)]]").Select
Selection.Cut
Range("Customize_Shortlist[[#All], [Super Sector]]").Select
Selection.Insert Shift:=xlToRight
End Sub