Excel VBA Macro: Pull Specific Columns from One Sheet to Another (Dynamically)
HTML-код
- Опубликовано: 1 окт 2022
- 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 (RUclips 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
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 👌👏
You completely blew my mind! This is outstanding. Thank you so much!!
I love it!! thank you for teaching us. you made it so simple and easy to understand and follow... great job!!
Really cool, straight to the point, great tutorial!
Appreciate the feedback 😊
@@greggowaffles Dim i As Integer
Dim j As Integer
Why are these letters used as the integer
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.
I really appreciate that! Thank you for the feedback
finally, got it!!! thanks. code works great 👍👍👍👍
Glad to hear that!! Thanks for watching!! 💪💪
Nicely done. Don't forget to set your objects to nothing before you exit the sub.
Thank you so much
You are very welcome!
superb! got a new sub here. Thanks!
Thank you so much!!
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.
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?
Teşekkür ediyorum.
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?
It's a great tutorial 👍
But can you tell us how to pull specific column with some applied filter? Thx
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
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.
same here :)
can this be modified to pull column names that are not an exact match?
Do you happen to have a way to pull columns into different sheets at once?
Thanx
No prob!
Hi, while running this VBA Macros. I'm receiving fix Run-time error '6' Overflow. How should I fix it. Please guide.
What if I want to pull a specific date or date range?
Row Count say shows an error " Runtime Error 6 / Overflow " .. What does this mean ??
Hi there, how do you deal with copying over a column that contains dates? Thanks
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?
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.
Could you use an advanced filter instead?
One specific column values are not getting copied. What do do?
Hi I got an error on this line " If ws2.Cells(1, i) = ws1.Cells(1, j).Text Then" - Run-time error '1004': Application-defined or object-defined error. How do i fix? Thanks in advance.
What to do if the value to be copied is a number and text, how would that be assigned?
Hi, I was using this code and it worked perfectly, but now I get an error code (400) can you help please
Hello, I'm getting error to use this macro, could you please help. Application-defined or object-defined error . I'm trying to put from sheet to another. Not workbook
What’s your code look like?
it would be a big help if you'll share the excel file with that VBA code. 😅😅😅
This doesnt work for me. I dont know the prob
Dim i As Integer
Dim j As Integer
Why are these letters used as the integer
Just a preference. You can use whatever letter/word you want
Man you can’t even imagine how I hated this video… sorry but who in the hell that doesn’t have any clue about formulas is going to be able to write all the formulas you wrote?
I thought the point of the video was just to apply a direct formula to pull the columns from one WS to another. Not helpful at all!
Tutorials are meant to be for people who DO NOT know anything about it.
Thank you for watching the video and sharing your feedback. I aim to be as descriptive as possible with the video titles so that people know exactly what to expect in the videos. I have beginner tutorials on my channel if you want to check them out, and if there’s a specific example you have I can try to help you out.