Straight up this man is a genius. I've been doing this manually for years. I figured out how to combine multiple workbooks but never multiple tabs within. This is classic. Thumbs up man!!
This has helped me so much ! If possible how would I have this to identify a specific criteria in a sheet ? I’m trying to create a report for my manager to use for quality and this has helped but it’s pulling cells with no useful data - blank fields with a formula in. Any advice would be amazing !
This is awesome. You are a super star. I have been looking for a way to combine large data from multiple worksheets for few days. You just make my day Dan.
Hey +Senthil Kumar.P dealing with selections only is very tricky since the user might mistakenly change his or her selection and not get the correct results. I would suggest modifying the code every time your requirements change to get the columns you need
Hi Dan..thanks aton...but i also want to delete the extra three sheets we had before because we already combines verything into one...so just wnat to have the import sheet and delete the rest of the sheets so can u also show us how we do that?
Hi +Marie-Line Skaff -- in that case, change line 30 from the one-liner "rngSrc.Copy Destination:=rngDst" to instead be two lines, "rngSrc.Copy" followed by "rngDst.PasteSpecial xlPasteValues"
Hi, thank you for this this helps me a lot. Though I have a question, What if I just only want to copy the data coming from other sheets based on my criteria. for example I only just want to copy the data that has a "running" status on Notes
Hey Dan, Lets say I have a workbook (WB1) where sheet1 lets me enter a # value (n), which will then generate n copies of a sheet from a separate workbook (WB2). So for example I enter '3' and I get 3 copies of WB2SHT1. WB1 now has 4 sheets, WB1SH1, and 3 copies of WB2SH1 (This is already done and works, everything from here on out is uncharted territory for me!). WB2SH1 has ~6 or 7 check boxes that will need to correlate with a small range of cells. Currently, the ranges for the check boxes are are in their own sheets in WB2 (sheet 2 thru 8). All the ranges are in same (A1:M12). From WB1, after entering the # of sheets of WB2SH1 I need, I will go into each copy, select the check boxes required for that sheet, and the selected ranges will populate below, starting at B11 on down (if 2 boxes are checked, the first checkbox range (ex, WB2SH2) will populate first on WB1SH2 @ B11, then the next check box range that is checked would populate directly beneath it @ B23). My VBA skills are non-existent (user for less than 12 hours). I managed to get this far by asking people on the internet and googling.
Hi +kge4mvp -- really appreciate you taking the time to write that all out, but I'm not sure I understand what you're asking for. Can you reach out to me via email with some examples? My contact info is at the bottom of this page: danwagner.co/
Hi, I need help with the blank cell issue. Scenario: I have a sheet with a blank cell in the center of the data. Details: If there is data from A2 to D25 and a cell B15 is blank. Now, when I am collating the data, the VBA code stops reading at the blank cell and stops consolidating. Query: How to copy the blank cell to the Import sheet. The Import sheet must contain the same data as in the original sheets, be it blank or some data. Please suggest.. and Thanks in advance.
Hi there Dan, I tried this and it worked. Can you tell me what code I would need to add to search for something specific in all the cells from all sheets? From your example, I want to only include the Reporter - FR from all sheets on the import sheet. Thank you.
Hey +Terry Moran -- I'm not sure I understand your question. Do you want to combine specific rows, or just specific cells maybe? Sending an example would be super helpful too, holler at me: dan@danwagner.co
Hello Dan... I seem to have run into some trouble. I'm handling a rather large set of data. When I run the code, Only the first column is being copied and combined whereas I want all the columns and all data to be combined into one sheet. I copy-pasted the code from the link you have given in the description of the video and have not made any changes. Please help
Hello +Abhijith Krishnakumar when the code is only copying one column of data the issue is usually that the first Sheet being analyzed for the last column and last row is blank -- check your file for empty (possibly hidden) Sheets and remove them or modify the code to check for cell values before each copy + paste
@@DanWagnerco Thank you... I worked out a way around it... Since my number of columns are fixed, I changed the code accordingly and it worked for me... But thanks for the prompt reply.. Ill check my file out for the said issues that may have caused the error :)
Hello Dan, thank you for this video....... my question is what you showed in your video all cells are filled so that's y it get copied if suppose some cells are blank in middle of data in sheets from which you are pulling data if you copy with this same VBA code ...while it copied same as it is (i means if cells are blank then in main sheet will it show blank or it will auto filled with other data which is data filled in next cell)... i want code if suppose if any columns contains blank cells then it showed be blank as it is when we pull the data form multiple sheets ...please help me in this.....
Hello +Flim_me I am sorry but I do not understand your question, as the code is written it will copy all cells in the Range whether they are empty or not
Dan, First, great tutorial, has helped a ton. I'm looking to consolidate the same tab "Data Tab" from multiple workbooks. Each of the separate workbooks contain several identical tabs and I would like to use a macro to generate a master data table. this video gets me part of the equation, hoping you could help me added wrinkle of multiple workbooks. Thanks!!
Hey +LeoT Lion -- this tutorial explains how to combine data from a single Worksheet in multiple Workbooks into a single master data table, if you combine that technique with the technique in this RUclips video you should be able to accomplish this combination: danwagner.co/how-to-combine-multiple-excel-workbooks-into-one-worksheet-with-vba/
Hi Dan, your video is great, this will help me a lot. I do have a question though, what about combining multiple excel sheets into 1 tab only? Do you have an easy way to do this? Thanks in advance.
hi +Laurice Lopinto, this tutorial explains how to combine multiple Excel Workbooks together into a single sheet: danwagner.co/how-to-combine-multiple-excel-workbooks-into-one-worksheet-with-vba/
Hy Dan! Thanks a lot! I have just one question: How can I past my data as Values in the Import sheet. In my case, my sources have formulas and I do not want them as formulas, just their values ...
HI Dan, based on the above worksheet, if there are more than three sheets (tabs) , let's say five different sheets, and I only want the first, second and last sheet of the same worksheet, where would I do that in the VBA?
Hi +ERLINDA ELORIAGA -- unfortunately Sheet order can change really easily, so I strongly suggest you use Sheet names rather than Sheet order to dictate this combination. I wrote a tutorial about combining some Sheets but not others here, and the technique described in that tutorial should work: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
I tried the same scenario (template) u taught without having any data in one sheet except the header, and after I ran the code, I could see the header has been copied to the import sheet. How can I avoid the column header to be copied if any of the three sheet is empty?
I know this video is old, but hopefully you're still active on here! The macro is running perfectly fine on my file, but not advancing to the additional sheets in my book. for instance, it'll bring in the sheet2 data, but not move on through sheets3-17.. any pointers? I copy and pasted the macro as you have it written.
Hi +Parker Richmond that might be caused by differences between sheet2 and sheets3-17 (do they have different columns populated?), if they are all the same I would insert some Debug.Print statements into the loop to see what is happening on all those other Sheets
Hey +jonathan marin -- this tutorial explains how to skip Sheets when combining data: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
Hi Dan, Is there any way to Import the data in a particular sequence from the individual sheets. Let's say "Peripherals" first and "IOAccess" second and "MemoryDisc" Third. Thanks in advance. What is your suggestion and sequence to learn VBA Macros from Scratch as I don't have any technical knowledge. Thanks very much for your Commendable videos which are really helping to minimize the manual effort.
Hey +reyman tan -- if you get a "Sub or Function not defined" error, that means you have not copied over ALL the code from the script in the linked tutorial. You need to make sure you copy everything, including the Functions defined at the bottom (these are Public Function LastOccupiedRowNum(Sheet As Worksheet) and Public Function LastOccupiedColNum(Sheet As Worksheet). Ensure that you have all the code copied into your module and you should be all set!
Hi +Alvin C -- if you only get the first column combined, that means line 13 ( lngLastCol = LastOccupiedColNum(wksDst) ) is evaluating to 1, which probably means your "Import" Sheet is empty. This script expects that your "Import" Sheet has the exact same header row, so to fix this you can copy over your header row from one of the data Sheets to your "Import" Sheet then re-run the script
Hi +Ademola Afolabi -- the article associated with this walk through, danwagner.co/how-to-combine-data-from-multiple-sheets-into-a-single-sheet/, includes that function. If you're having a hard time finding it there, this should do the trick: gist.github.com/danwagnerco/040402917376969bf362#file-combine_data_from_all_sheets-vb
I've add a snapshot from a file. So end of Mei I want to Copy the data from range "U5:U106", End of June I want to copy range "V5:V102" etc, etc, and copy this into antoher workbook. The problem is how can I pre-define the range based on a cell in the file? E.g.: Reporting Month: "mei-18" in cell "AA5" then the macro will select range "U5:U102" and copy. Or with a Msgbox. etc. Who can help?
hi +reefrøcks -- you'll want to change line 30 into two lines, the first "rngSrc.Copy" (copy the source range) and the second "rngDst.PasteSpecial xlPasteValues" (paste the data to the destination range as values)
Hi Sir, I hope you are doing well. I have multiple excel file in different location with Password protection (Each Diffrent Password). End of the day i want to copy paste data in one master file. All file data Headers Same. Every time i have to enter password and open file for copy paste to Master file. Kindly help me out automated Macro VBA code for this.
Hi Dan! Thank you for great VBA videos! I have a question. The code is failing when I have data to the right in my paste sheet. The code then puts the data in the first date cell after my data to the right of the paste area. How should I change the the last occupied formula to only look at specific range?
Hi +Mary Jane are you absolutely sure you copied all the code? That error "sub or function not defined" means VBA is not recognizing a function which is generally an issue when you do not copy ALL the code (chances are you missed the functions at the very bottom) at danwagner.co/how-to-combine-data-from-multiple-sheets-into-a-single-sheet/
@@binguyen3251 are you absolutely, positively, 100% sure you copied every single line from the code at danwagner.co/how-to-combine-data-from-multiple-sheets-into-a-single-sheet/ ? There are two functions defined at the bottom, lines 43 through 85, that are vital to running this script, and if you did not copy them over then you will get an error message stating "Sub or Function not defined"
Hey Dan, I am trying to copy your code but I am getting a compile error for lngDstLastRow = LastOccupiedRowNum(wksDst) As the system is saying it is not defined. Rest of code is copied as shown so I am not sure how to fix.
hey James are you absolutely sure you copied all the code? that is a function that is defined at the bottom of the script - double-check the copy paste from the tutorial, could be as easy as some missed lines
Hey, The whole copy-paste procedure loops twice, so i get double the data i actually want. Do you have any idea what i might have done wrong? The code is exactly the same as yours. Thanks.
hey +Killer Kuerbis -- fascinating! that is literally the first time i've heard that the code is looping twice... in order to troubleshoot that condition, you should double-check the For Each wksSrc In ThisWorkbook.Worksheets loop with a Debug.Print statement as that defines the start and stop. if that is functioning correctly, you need to double-check the code inside the loop, as you may be executing the copy and paste twice
Dan, Please let me know the basic code to run macros on shared workbooks. i have just google a code for auto save the shared workbook every 5 mins and after sharing enabled seems to be macro is missing from workbook.
hi +muthu kumaran I don't have the answer to your question, in general I would strongly suggest using Excel 2016's built-in Auto Save functionality instead of attempting to write a macro
hi dan, if i have two sheets but different columns for example one sheet(amount, price, name) sheet two(amount, date, note) how cam i marge this two with macro thanx
Heya +Vadim br -- merging two sheets with different headers is very doable. However, you have to decide how you would like them to be merged -- do you want to maintain all of the columns?
Hey +Vadim brs! I tackled this challenge (where columns are out-of-order) here: danwagner.co/how-to-combine-data-from-multiple-sheets-into-a-single-sheet-but-different-columns/ Here's a link to the video guide: danwagner.co/screencast-combine-sheets-with-different-columns
Howdy +Vadim brs -- apologies, the last comment has the incorrect link. This should do: Tutorial: danwagner.co/how-to-combine-data-with-different-columns-on-multiple-sheets-into-a-single-sheet/ RUclips: danwagner.co/screencast-combine-sheets-with-different-columns Thanks!
Hi dan, Daniel here what an amazing video to learn from. I have an issue though the data inputs in my case are filtered after excluding some rows but all my input sheets are having same columns but then after filtering about 21 sheets my range in each sheets differs can please help me getting a workaround for this as I want to combine the filtered data to a summary sheet.
hi +cool.danny 007 -- you can adjust the rngSrc variable in this code to instead use the Offset logic defined in this tutorial on line 31: danwagner.co/how-to-delete-rows-with-range-autofilter/ except you do NOT want to delete, you want to copy :)
Hi Dan, The code is very good. After combined in the import sheet, could there be a column that can show can show the initial data sheet name for references ? i.e. New Column in the import sheet listed the sheet name of their initial source of the information.
hiya +Win Tong Lim -- yes, this is possible! You can use the technique on lines 93-119 in this tutorial to accomplish that: danwagner.co/how-to-combine-multiple-excel-workbooks-into-one-worksheet-with-vba/
Hi This is very helpful! I'm running into an issue where the macro is ignoring my last occupied column and only taking the column before the last one. The src ends on column X but the macro only copies through column W. Also, some sheets don't have data in column X as this is a variable column. HELP PLEASE!
Hey +Rochelle Felipe -- this script only calculates the last column once, so I'm guessing that the first sheet does NOT have data in column X (so it thinks the last column is always W). This tutorial handles situations like yours, where the columns may vary: danwagner.co/how-to-combine-data-with-different-columns-on-multiple-sheets-into-a-single-sheet/
Hey +Vikram Kanda if you only need 4 columns, I would suggest copying all the data over (like the tutorial) then deleting the columns you do not need at the end of the script
if on a given table there are empty cells throughout various columns/rows (and we want to keep it as it is), how would you overcome the issue of the code stopping as soon as it reaches the first empty cell?
hi +daringwrinchester -- the code as-written should find the very last occupied cells both in the X and Y directions, then copy the ENTIRE range, leaving your empty cells intact. if it is not doing that, something is majorly wrong! what happens when you run the code as-is?
Hey +Chris Poulos -- if the script is only copying the first row, that means lngSrcLastRow = LastOccupiedRowNum(wksSrc) is 1 which is very strange, I suggest adding break points and examining that variable as you loop through each Sheet to see why lngSrcLastRow is equal to 1 each time
Chris Poulos OK awesome, when only the first column is copied that probably means the first Sheet being examined is empty, so the last column variable is set to 1. Look for an empty Sheet in your file and remove it OR make sure you’re skipping that Sheet in the loop
Hey @Rian Sutarsa great question! You can use an If statement to check the Sheet names and determine whether or not you want to combine that Sheet like I show in this tutorial: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
Hi Dan, This is awesome! So helpful for a newbie. I was wondering if there is an easy way to specify the sheets I wanted rather than looping through all sheets. I have a workbook with 8 tabs or so but need to do this with just 4 of the tabs. Again thank you so much for this video.
Hey Jackson -- great question. If you only want to combine 4 specific tabs (amongst many), you'll need to tweak line 22 in this macro (which is available here: gist.github.com/danwagnerco/040402917376969bf362). Rather than skipping the "Import" sheet, which is accomplished on line 22 by If wksSrc.Name "Import" Then, you'll instead want to verify that the name matches one of your sheet names by using "=" and "or": If wksSrc.Name = "SheetYouWantToImport1" Or _ wksSrc.Name = "SheetYouWantToImport2" Or _ wksSrc.Name = "SheetYouWantToImport3" Or _ wksSrc.Name = "SheetYouWantToImport4" Then
Hi Dan. I've 'borrowed' your VBA with line 22 amended so that it only picks up nominated sheets and it works perfectly. With my very limited VBA knowledge how would I need to amend the VBA to (a) clear the records on the 'Import' sheet (except headers) every time I run the VBA, i.e. I don't want to append the data on the 'Import' sheet but start with a clean sheet every time the VBA runs? and (b) how can I nominate a different starting column on the 'Import sheet', i.e. my source sheets all have columns A to D but in my destination sheet ('Import') the data needs to go into columns H to K?
@@DanWagnerco Hi Dan! Thanks for sharing this code but I have changed the formula with (=) sign. However, its only copying the first column. what should be done next to copy the and paste the all columns in source_sheet?
@@javalearner4227 If the script is only copying a single column, that means it is probably starting on a blank Sheet and calculating that the last-occupied column is 1. I suggest removing the (probably) empty Sheet or skipping it -- thanks!
Hello Dan,I keep getting the message below: Run-Time Error '9':Subscript out of rangeVB highlights line 11:Set wksDst = ThisWorkbook.Worksheets("Import")not sure if anyone else got this error. It is clearly within range but not sure what is going on.
Found the issue... I was loading the code into PERSONAL.XLSB since I wanted to use into any open workbook but since the code is defined to one particular workbook I was getting the out of range message.... anyone encountering this same issue... if you want to use this code to any open workbook simply set the ("Import") to ("Sheet1") and make sure the entire code is in the PERSONAL.XLSB. and do not change the title of the sheet until after you're done running the macros. if there's a better and easier way please post.
Heya +Bernardo Reyes -- awesome detective work! Since you'd like this code to reside in your personal.xlsb, "ThisWorkbook" will no longer be the target of all the operations -- the source of the problem, as you accurately identified. If you are 100% sure that you will only execute this code on active workbooks that need combining, you could change "ThisWorkbook" to "ActiveWorkbook" and make sure the destination Sheet name is set appropriately (since it probably will not be "Import"). Hope that helps!
Hi +Nilesh Kadu you can follow this tutorial to see how to skip certain Sheets but not others when combining data: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
Hi Dan, If I only want to copy the data start from sheet #3 to the last sheet (the # of last sheet is varied in each file)... what should I do? Thank you.
Hiya +Prelude Fugue, I'm sure this challenge can be handled, but I need to know a bit more about the problem at hand. Are the sheets named the same way, like "Sheet3", "Sheet4", "Sheet5", ... "SheetX"?
Hi Dan, thank you for your reply!! The name in those sheets are different... We created these workbook for tracking Department sales. so each department has its own tabs..... Also each workbook might have varied number of tabs depends on how many departments in that locations.. The only commend thing is... I only need to copy the data start from the 3rd sheets (count from left) since the first 2 sheets are not department sales........also the columns and columns heads(row1) in these Department sales sheets are exactly same... When I copy these department sales into a "summary sheet", I wish to copy the formula as well, so if I update any of these sheet, the summary sheet will also be updated..... Is these possible ??/ I am so new to Macro and totally lost
Hey +Prelude Fugue, totally possible and this situation is not that uncommon. It sounds like you need to skip the first 2 sheets, and they are pretty well-known... if you know the names of the 2 sheets, you can use the strategy in this tutorial: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
Hey +Harijs Osis -- LastOccupiedRowNum is a function defined at the bottom of the code in the tutorial, are you absolutely certain that you copied EVERYTHING? Here is a direct link, do you still get the error when you copy all the code (from line 1 through line 85)? danwagner.co/how-to-combine-data-from-multiple-sheets-into-a-single-sheet/
Hey Albert -- excellent q! (Please pardon any typos as this comment is coming from a phone) You'll need to tweak line 30 into two lines: rngSrc.Copy rngDst.PasteSpecial xlPasteValues That should do the trick!
hi +Brenson Saint Jean I would use the Range.AutoFilter method, this tutorial covers that method in the context of deleting rows (but it's the same concept): danwagner.co/how-to-delete-rows-with-range-autofilter/
Hey +Vipin Sahu this tutorial explains how to combine data from multiple Sheets into a single Sheet even when the columns are out of order: danwagner.co/how-to-combine-data-with-different-columns-on-multiple-sheets-into-a-single-sheet/
I've applied this to my workbook and ran into a few small issues, but i'm stuck at 3). Can you help please? 1) Firstly, I wanted this script to only target sheet names containing "CDK" within my workbook 'Loop through all sheets For Each wksSrc In ThisWorkbook.Worksheets If wksSrc.Name Like "CDK*" Then 2) Secondly, I wanted the script to paste values the destination sheet only 'Store the source data then copy it to the destination range With wksSrc Set rngSrc = .Range(.Cells(2, 1), .Cells(lngSrcLastRow, lngLastCol)) rngSrc.Copy rngDst.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone End With 3) Thirdly and most importantly, the script is only pasting column A of my source sheets to column A of my destination sheet. My source sheets contain 7 columns worth of data...
hi +Ashley Townsend -- for your first question, you want to use InStr to check for "CDK" in the Worksheet name. Here's an example: stackoverflow.com/a/15585089/3075291
hey +Ashley Townsend -- also for your first question, here's a tutorial I wrote on combining certain Sheets but not others: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
hello +Ashley Townsend -- this script calculates the last occupied column based on your "Import" Sheet, which I am assuming is empty in your case (meaning that the function returns a 1) but in this example, the "Import" Sheet already has headers, meaning that the last-occupied column calculates correctly. To fix this you should either add headers to your "Import" Sheet or calculate the last occupied column from the first Sheet that actually contains data
Thanks for the really quick replies...! I failed to mention that i'm a VBA newb. Both of my import sheets that I'm testing this on aren't empty, they both contain data from Columns A:G
Hiya +Marcus Kay -- this ought to help: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/ Good luck and don't hesitate to holler at your boy if you have any additional questions, Dan
Hi +Eamon McCourt -- that happens when your "Import" Sheet is left blank, forcing the lngLastCol value to 1. To fix this you'll either want to (1) drop the headers into your "Import" Sheet like I do in this example by hand or (2) modify the code to calculate the last-occupied column on the first data Sheet
Will this work when each sheet has extra columns. I want to look at copying all columns from multiple workbooks/sheets over wherever each column matches.
hi +Zach Clement no this tutorial assumes that all sheets have the same column order, this tutorial handles out-of-order and additional columns: danwagner.co/how-to-combine-data-with-different-columns-on-multiple-sheets-into-a-single-sheet/
Dan Wagner I am looking for a macro code that can copy columns that match based on the destination workbook. It is key that I paste this data to existing placeholder columns in my destination workbook . I also need the macro to loop and do this for several different worksheets that have differing column name combinations. Note: all of the columns of the columns I want copied over are in the destination workbook already.The unique thing is my destination file has a placeholder for all of the differing worksheet column name combinations. I cannot seem to find a macro anywhere on the internet that does this function. The macro would need to sense where data stops in the destination workbook so it can paste the next.
heya +Zach Clement -- fascinating and well-described! I have not written a tutorial on that exact case, but it can absolutely be done. I'm testing out a handful of packaged services that might be interesting to you, email me at dan@danwagner.co if you'd like to discuss further
+DigitalErra Hiya DigitalErra -- in the code used for this video, duplicates are allowed. Do you need to remove duplicates? As far as skipping tabs goes, you can check names in the For Each wksSrc In ThisWorkbook.Worksheets loop. Is skipping tabs something you need to do often?
+Dan Wagner I would have charts and other data that will not need to be copied over. I would need columns A-M to be copied, and 31 tabs to represent everyday of the month all at various lengths. I could send you over the sheet that I will use daily.
Hi Dan! this was extraordinarily helpful. QQ - I have a LARGE workbook, over 40 tabs. I need to combine 3. How can I amend so that instead of saying EXCLUDE these 37 tabs, I can ask it to INCLUDE only these 3 named sheets. Thanks in advance. 'the names we know we DO NOT want to combine into ALPHA For Each wks In ThisWorkbook.Worksheets
'If this sheet name is NOT one that we want to ignore, 'append the data to the ALPHA sheet strName = UCase(wks.Name) If strName "Sheet1" And _ strName "Sheet2" And _ strName "Sheet3" And _ ........ to Sheet 44
Hey +Shannon Ryan, in this case you will instead want to use strName = "WhateverName1" Or strName = "WhateverName2" Or strName = "WhateverName3". Thanks! -Dan
In my workbook, the macro stops at line Set wksDst.. with the error message Run-time error '9'; Subscript out of range. I have named a blank sheet "Import" Any idea what is wrong? Thank you.
Hey +robert cline -- is the "Import" worksheet inside the Workbook that you are running the macro from? That's what ThisWorkbook.Worksheets("Import") is assigning
That's a puzzler +robert cline, I can't troubleshoot further without looking at the file. Can you send it to me? My email is at the bottom of that article
I have 18 columns. Column A to R. On some sheets, there are two blank columns then a pivot table in column/row T1 on the same sheet. Could that be the issue?
Since you responded to Rochelle that the code only computes the columns once, I places a sheet with only columns A-R and no other data as the first sheet after "Import" sheet.
I am very new to VB and i don't know much. But using your video i tried combining data into one. But i got a message saying sub or function not defined. Please help me solve the issue
Hiya +latesh shetty -- not to worry, I think I know what the problem is. Did you definitely copy all of the code from the example write-up in the linked tutorial? Here is a link to that code direct: gist.github.com/danwagnerco/040402917376969bf362 It is possible that you missed one or both of the functions defined and implemented at the bottom ("LastOccupiedRowNum" and / or "LastOccupiedColNum").
Can I synchronize the import sheet and source sheet? which means I want to keep inputing new data into source sheet and I want the new data shows in the import sheet too
Hi Dan! Thanks for this! Its super helpful! 2 questions. How can I set it up so that's its checking every sheet except for 1 sheet?How can I restrict the range in each sheet that its looking to? I want it to pull data from a smaller range of rows.
Hiya +Lauren Henderson! (1) Here's a tutorial I wrote about combining certain sheets, but not others: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/ Of course, if you'd prefer to go straight to the video, it's here: ruclips.net/video/ZRKk2XHwUX4/видео.html (2) Can you tell me more about the range on each sheet? Is it always the same?
Hi Dan, The range is always the same. I have 10 or so sheets with 2 separate tables that each client fills out, and I need to extract the data from only one table onto a summary sheet,
Hey +Lauren Henderson -- cool, that is very doable! You'll want to change line 29 to be something like: Set rngSrc = .Range("A1:G20") where your well-defined Range can be any start / stop cells (like "B5:C10", "Z1:AA3", etc.)
Awesome! That helps a ton. What is the code I would use in order to get the macro to skip all rows from the table that are blank. I want it to extract any row of data that has at least one cell filled in from the table. And I want it to skip any rows that have all empty cells.
Hello sir I am Tushar Sharma from Kanpur I want create a master of ms excel. I have workbook having 500 sheet. I want to prepare a list of headings with thier values I am unable to use VBA coding By cut copy paste its hue stuff and time taking. By dragging sheet no. Not increasing except cell no increased. Example : =sheet1! B1 By dragging :=sheet1! B2 I want : =sheet2! B1 So please help me
Hi +kanmani chinnadurai -- please follow this tutorial to combine Sheets from multiple Workbooks into a single file: danwagner.co/how-to-combine-multiple-excel-workbooks-into-one-worksheet-with-vba/
Dan, I emailed you, but thought I would ask my question here too. What if the only information I want to copy is in cells A4:V4 on thirty different sheets?
Hi there, I have the same problem. this can be resolve by checking this line 29 where "Set rngSrc = .Range(.Cells(2, 1), .Cells(lngSrcLastRow, lngLastCol))" The above has set the range base on the predefined rules i.e cells that contain data. But to set the copy area as A4:V4, it would be read as "Set rngSrc = .Range(.Cells(4, 1), .Cells(4,22))" Hope this help.
hi +Alex Allen this tutorial explains how to skip more than one Sheet: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
Hi +KRISHNA JAGADEESH you can get the VBA code from the first link in the description which points to the tutorial here: danwagner.co/how-to-combine-data-from-multiple-sheets-into-a-single-sheet/
Hi! is there a way to insert some line of code to insert index match or static commands? for instance i would want to insert a date in column A based on the date on that worksheet. Thanks!
Hey +Chrissy Lynch -- I'm not sure I understand your question. Do you want to add columns to the resulting combined data? Or into the source data on the individual sheets?
Dan Wagner I want to add data to the combined results tab. so for instance I would insert a date in column a and the word lunch in column c and column b would be what was copied from the individual tab. I hope that makes more sense. thank you!
hey +mithun nair -- yes, you can skip sheets by following the tutorial here: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
Dan Wagner - ha thanks for such a quick reply... i am very much new to this .. let me go through it and will get back to you if i had any doubt ... thanks and good luck
Ha Dan Wagner it worked for me in a different way... I used ur 2nd macro it started skipping unwanted sheets as expected but not combining all the sheets to one excel.. then I pick up 'strName' into old macro and it worked successfully well but, a small issue is that it copy the header again which is getting duplicated in the master sheet... I added a 'selection.delete' into it just before 'MsgBox' and it actually not the right approach but it's working as of now. But let me know if we can stop this by modifying the macro Now one more issue is that, if I run the macro again or I hit it twice accedently, it will copy the data from other sheets again into master sheet creating doubling all the data. And create duplicates .... Will u be able to help me on this.. So that there will be some mechanism to prevent the data from duplicating
Hi +mithun nair -- I'm not sure exactly why you are getting multiple headers as I cannot see your code, but in general you can avoid header copying in a loop by using a strategy like lines 60-62 from this link: gist.github.com/danwagnerco/a5be4bd8e4843533a096f9ab91bcb3ed#file-combinemanyworkbooksintooneworksheet-vb
Hi +Tra Pham -- if it only copies 1 column, the issue is almost certainly that lngLastCol (variable) is not getting correctly calculated. Do you need to skip certain worksheets, or is there perhaps a hidden worksheet that is empty?
I want to copy all the data from several worksheets to a master worksheet. Let's say an individual worksheet will have data in only 3 columns name, possible points and total points. Another worksheet will have the same column names and many more. I want all these information no merged but displayed individually but on the same master worksheet. So the master sheet will have all teams separately displaying all the information next to each other to compare. 1st showing the first worksheet and next the 2nd worksheet data. I hope it makes sense. I have found videos on how to merge, consolidate, get ranges, etc... but I haven't found anything to do what I'm trying to do. If it's done automatically it would be best. A different person will be adding information to each worksheet and all the data from all the worksheets should be automatically updated to the main worksheet to see all groups next to each other and compare. This is for a competition. is VBA the only way to accomplish this?
Hey +Miguel Angel Torres Espinoza -- you can use this strategy to copy all columns from all sheets, one on top of the other, with columns matched where appropriate: Tutorial: danwagner.co/how-to-combine-data-with-different-columns-on-multiple-sheets-into-a-single-sheet/ Screencast on RUclips: danwagner.co/screencast-combine-sheets-with-different-columns
thanks, I will try it. Although, I think it would be more efficient to add them next to each other because each worksheet only has 3 columns and i would be able to see all the teams without having to scroll down. I could just use one worksheet are and see all the teams at the same time. is that possible?
Each worksheet has 3 columns: name, possible points and total points. I'm using column A for numbers 1-100 and column B,C, and D for the data. I want to be able to displayed the data from each team on one main worksheet next to each other. I could use columns B,C,D for one team and leave a space and next F,G,H for second team, etc... up to 20 teams so I can compare scores. Is it possible to transfer data from columns B,C,D to other letters? Example: team 1 columns A,B,C. Team 2 E,F,G team 3 H,I,J, etc... Does it make sense?
Hi +Dan Wagner. Thanks for the tutorial but it combines the data. I don't need to combine the data. I just need to display the data from all worksheets next to each other to compare them. I want to be able to see all teams together from left to right but not combined. someone will be inputing data in every single worksheet. All the data from all teams "single worksheets with only 3 columns" should appear all together on the main worksheet as mentioned before. Not combined, not merged, not consolidated. Just displayed exactly as they are next to each other to compare them. I tried creating a link but once I uploaded to dropbox there's a message that says that the browsers don't support links and are disables. It only works in my mac. I'm trying to do somehting that will work permanently even if i share the file in dropbox to be update by other people and update automatically on the main worksheet will all the results from all teams to get the scores for all the teams. I hope it makes sense.
HI +Paul Haslam, are you absolutely certain that you copied all of the code? That error occurs when LastOccupiedRowNum, which is a function defined at the very bottom of the code, is not copied over
Set rngSrc = .Range(.Cells(2, 1), .Cells(lngSrcLastRow, lngLastCol)) rngSrc.Copy Destination:=rngDst - this piece of code looks at little weird to me, but I am no expert. commas followed by dots?
Straight up this man is a genius. I've been doing this manually for years. I figured out how to combine multiple workbooks but never multiple tabs within. This is classic. Thumbs up man!!
Thanks so much +Gabriel Cortes, really appreciate the kind words!
Hi Dan, is there a way to do this but to do it so it pastes values and does not paste the formulas within the cells?
This has helped me so much ! If possible how would I have this to identify a specific criteria in a sheet ? I’m trying to create a report for my manager to use for quality and this has helped but it’s pulling cells with no useful data - blank fields with a formula in. Any advice would be amazing !
HI, i am getting error as Sub or Function not Defined and getting highlighted "LastOccupiedRowNum" please suggest how to solve this
With your code, how can I add a new column and show each tab name to each row on the "Import" tab where all consolidated data is
This is awesome. You are a super star. I have been looking for a way to combine large data from multiple worksheets for few days. You just make my day Dan.
Thanks so much +Uzana Tonmukayakul!
Hello bro, can this be modify to copy only rows base only on the specific date that I wanted
Dear
Dan Wagner,
I want to copy selected column range only. How to modify the code according to my requirement
Hey +Senthil Kumar.P dealing with selections only is very tricky since the user might mistakenly change his or her selection and not get the correct results. I would suggest modifying the code every time your requirements change to get the columns you need
Hi Dan..thanks aton...but i also want to delete the extra three sheets we had before because we already combines verything into one...so just wnat to have the import sheet and delete the rest of the sheets so can u also show us how we do that?
IZ THERE AN ONLINE WEBSITE WHERE I CAN RUN VBA CODES......AND IT WILL CORRECT ME ACCORDINGLY
Hello Dan! Thank you for this video! It helped so much! How can I copy the values only, not the whole formulas and links in the cells?
Hi +Marie-Line Skaff -- in that case, change line 30 from the one-liner "rngSrc.Copy Destination:=rngDst" to instead be two lines, "rngSrc.Copy" followed by "rngDst.PasteSpecial xlPasteValues"
Hi, thank you for this this helps me a lot. Though I have a question, What if I just only want to copy the data coming from other sheets based on my criteria.
for example I only just want to copy the data that has a "running" status on Notes
Hey Dan,
Lets say I have a workbook (WB1) where sheet1 lets me enter a # value (n), which will then generate n copies of a sheet from a separate workbook (WB2). So for example I enter '3' and I get 3 copies of WB2SHT1. WB1 now has 4 sheets, WB1SH1, and 3 copies of WB2SH1 (This is already done and works, everything from here on out is uncharted territory for me!). WB2SH1 has ~6 or 7 check boxes that will need to correlate with a small range of cells. Currently, the ranges for the check boxes are are in their own sheets in WB2 (sheet 2 thru 8). All the ranges are in same (A1:M12). From WB1, after entering the # of sheets of WB2SH1 I need, I will go into each copy, select the check boxes required for that sheet, and the selected ranges will populate below, starting at B11 on down (if 2 boxes are checked, the first checkbox range (ex, WB2SH2) will populate first on WB1SH2 @ B11, then the next check box range that is checked would populate directly beneath it @ B23).
My VBA skills are non-existent (user for less than 12 hours). I managed to get this far by asking people on the internet and googling.
Hi +kge4mvp -- really appreciate you taking the time to write that all out, but I'm not sure I understand what you're asking for.
Can you reach out to me via email with some examples?
My contact info is at the bottom of this page: danwagner.co/
Hi,
I need help with the blank cell issue.
Scenario: I have a sheet with a blank cell in the center of the data.
Details: If there is data from A2 to D25 and a cell B15 is blank.
Now, when I am collating the data, the VBA code stops reading at the blank cell and stops consolidating.
Query: How to copy the blank cell to the Import sheet.
The Import sheet must contain the same data as in the original sheets, be it blank or some data.
Please suggest.. and Thanks in advance.
Hi there Dan, I tried this and it worked. Can you tell me what code I would need to add to search for something specific in all the cells from all sheets? From your example, I want to only include the Reporter - FR from all sheets on the import sheet. Thank you.
Hey +Terry Moran -- I'm not sure I understand your question. Do you want to combine specific rows, or just specific cells maybe? Sending an example would be super helpful too, holler at me: dan@danwagner.co
How can I combine multiple sheets into one sheet with VBA but only pull specific columns into the one sheeter?
Hi +LO Jessick -- I suggest combining all columns (like this VBA does), then deleting the columns you do not need once the combination is complete
Hello Dan... I seem to have run into some trouble. I'm handling a rather large set of data. When I run the code, Only the first column is being copied and combined whereas I want all the columns and all data to be combined into one sheet. I copy-pasted the code from the link you have given in the description of the video and have not made any changes. Please help
Hello +Abhijith Krishnakumar
when the code is only copying one column of data the issue is usually that the first Sheet being analyzed for the last column and last row is blank -- check your file for empty (possibly hidden) Sheets and remove them or modify the code to check for cell values before each copy + paste
@@DanWagnerco Thank you... I worked out a way around it... Since my number of columns are fixed, I changed the code accordingly and it worked for me... But thanks for the prompt reply.. Ill check my file out for the said issues that may have caused the error :)
Hello Dan,
thank you for this video.......
my question is what you showed in your video all cells are filled so that's y it get copied if suppose some cells are blank in middle of data in sheets from which you are pulling data if you copy with this same VBA code ...while it copied same as it is (i means if cells are blank then in main sheet will it show blank or it will auto filled with other data which is data filled in next cell)...
i want code if suppose if any columns contains blank cells then it showed be blank as it is when we pull the data form multiple sheets ...please help me in this.....
Hello +Flim_me I am sorry but I do not understand your question, as the code is written it will copy all cells in the Range whether they are empty or not
Dan,
First, great tutorial, has helped a ton.
I'm looking to consolidate the same tab "Data Tab" from multiple workbooks. Each of the separate workbooks contain several identical tabs and I would like to use a macro to generate a master data table. this video gets me part of the equation, hoping you could help me added wrinkle of multiple workbooks.
Thanks!!
Hey +LeoT Lion -- this tutorial explains how to combine data from a single Worksheet in multiple Workbooks into a single master data table, if you combine that technique with the technique in this RUclips video you should be able to accomplish this combination: danwagner.co/how-to-combine-multiple-excel-workbooks-into-one-worksheet-with-vba/
I really want to thank you, you're a life saver. Video uploaded Feb 2016 and here's my comment dated Feb 2021.
Thanks +marvin ignacio I am really happy to hear that this has stood the test of time and helped you out!
I would like to ask you for VBA code which copy some data from multiple sheets into one sheet
Hi Dan, your video is great, this will help me a lot. I do have a question though, what about combining multiple excel sheets into 1 tab only? Do you have an easy way to do this? Thanks in advance.
hi +Laurice Lopinto, this tutorial explains how to combine multiple Excel Workbooks together into a single sheet: danwagner.co/how-to-combine-multiple-excel-workbooks-into-one-worksheet-with-vba/
Hy Dan! Thanks a lot! I have just one question: How can I past my data as Values in the Import sheet. In my case, my sources have formulas and I do not want them as formulas, just their values ...
HI Dan, based on the above worksheet, if there are more than three sheets (tabs) , let's say five different sheets, and I only want the first, second and last sheet of the same worksheet, where would I do that in the VBA?
Hi +ERLINDA ELORIAGA -- unfortunately Sheet order can change really easily, so I strongly suggest you use Sheet names rather than Sheet order to dictate this combination. I wrote a tutorial about combining some Sheets but not others here, and the technique described in that tutorial should work: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
Dan Wagner thanks very much for your time in replying, appreciate it.
I tried the same scenario (template) u taught without having any data in one sheet except the header, and after I ran the code, I could see the header has been copied to the import sheet. How can I avoid the column header to be copied if any of the three sheet is empty?
I know this video is old, but hopefully you're still active on here! The macro is running perfectly fine on my file, but not advancing to the additional sheets in my book. for instance, it'll bring in the sheet2 data, but not move on through sheets3-17.. any pointers? I copy and pasted the macro as you have it written.
Hi +Parker Richmond that might be caused by differences between sheet2 and sheets3-17 (do they have different columns populated?), if they are all the same I would insert some Debug.Print statements into the loop to see what is happening on all those other Sheets
Hi Dan, How can i make this code paste as values and only copy 2 sheets instead of every sheet in the workbook
Hey +jonathan marin -- this tutorial explains how to skip Sheets when combining data: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
Hi Dan, Is there any way to Import the data in a particular sequence from the individual sheets. Let's say "Peripherals" first and "IOAccess" second and "MemoryDisc" Third. Thanks in advance.
What is your suggestion and sequence to learn VBA Macros from Scratch as I don't have any technical knowledge.
Thanks very much for your Commendable videos which are really helping to minimize the manual effort.
Hi Dan. I get a Sub or Function not defined error.
Hey +reyman tan -- if you get a "Sub or Function not defined" error, that means you have not copied over ALL the code from the script in the linked tutorial. You need to make sure you copy everything, including the Functions defined at the bottom (these are Public Function LastOccupiedRowNum(Sheet As Worksheet) and Public Function LastOccupiedColNum(Sheet As Worksheet). Ensure that you have all the code copied into your module and you should be all set!
Hi Sir, I tried this but only first columns were combined.. please help
Hi +Alvin C -- if you only get the first column combined, that means line 13 ( lngLastCol = LastOccupiedColNum(wksDst) ) is evaluating to 1, which probably means your "Import" Sheet is empty. This script expects that your "Import" Sheet has the exact same header row, so to fix this you can copy over your header row from one of the data Sheets to your "Import" Sheet then re-run the script
Sir, the function LastOccupiedColNum() is missing in the example. can you please help with that as well?
Hi +Ademola Afolabi -- the article associated with this walk through, danwagner.co/how-to-combine-data-from-multiple-sheets-into-a-single-sheet/, includes that function. If you're having a hard time finding it there, this should do the trick: gist.github.com/danwagnerco/040402917376969bf362#file-combine_data_from_all_sheets-vb
I've add a snapshot from a file. So end of Mei I want to Copy the data from range "U5:U106", End of June I want to copy range "V5:V102" etc, etc, and copy this into antoher workbook. The problem is how can I pre-define the range based on a cell in the file? E.g.: Reporting Month: "mei-18" in cell "AA5" then the macro will select range "U5:U102" and copy. Or with a Msgbox. etc. Who can help?
Hey, do you know what should I change if I want to paste these as values? I get #REF! error when I run this script. Kind regards
hi +reefrøcks -- you'll want to change line 30 into two lines, the first "rngSrc.Copy" (copy the source range) and the second "rngDst.PasteSpecial xlPasteValues" (paste the data to the destination range as values)
Hi Sir,
I hope you are doing well.
I have multiple excel file in different location with Password protection (Each Diffrent Password). End of the day i want to copy paste data in one master file. All file data Headers Same. Every time i have to enter password and open file for copy paste to Master file. Kindly help me out automated Macro VBA code for this.
Hi Dan! Thank you for great VBA videos! I have a question. The code is failing when I have data to the right in my paste sheet. The code then puts the data in the first date cell after my data to the right of the paste area. How should I change the the last occupied formula to only look at specific range?
Hi Sir. I copied the code from your website and edit the neccessary however, im getting an error "compile error: sub or function not defined"
Hi +Mary Jane are you absolutely sure you copied all the code? That error "sub or function not defined" means VBA is not recognizing a function which is generally an issue when you do not copy ALL the code (chances are you missed the functions at the very bottom) at danwagner.co/how-to-combine-data-from-multiple-sheets-into-a-single-sheet/
Have the same issue. It stops at lastoccupiedcolnuminrow
@@binguyen3251 are you absolutely, positively, 100% sure you copied every single line from the code at danwagner.co/how-to-combine-data-from-multiple-sheets-into-a-single-sheet/ ? There are two functions defined at the bottom, lines 43 through 85, that are vital to running this script, and if you did not copy them over then you will get an error message stating "Sub or Function not defined"
Hey Dan, I am trying to copy your code but I am getting a compile error for lngDstLastRow = LastOccupiedRowNum(wksDst) As the system is saying it is not defined. Rest of code is copied as shown so I am not sure how to fix.
hey James are you absolutely sure you copied all the code? that is a function that is defined at the bottom of the script - double-check the copy paste from the tutorial, could be as easy as some missed lines
Thanks I found your online tutorial with the linked information from GITHUB.
Hey,
The whole copy-paste procedure loops twice, so i get double the data i actually want.
Do you have any idea what i might have done wrong?
The code is exactly the same as yours. Thanks.
hey +Killer Kuerbis -- fascinating! that is literally the first time i've heard that the code is looping twice... in order to troubleshoot that condition, you should double-check the For Each wksSrc In ThisWorkbook.Worksheets loop with a Debug.Print statement as that defines the start and stop. if that is functioning correctly, you need to double-check the code inside the loop, as you may be executing the copy and paste twice
Dan Wagner thanks for being so active here. I fixed one problem by just looking at your answers here.
Thanks for now, i'll check the loop again.
Dan Wagner actually after some debugging the problem was gone. No idea why it works now, classic programming.
isn't it so weird how that happens +Killer Kuerbis haha?! happy to hear that you have it sorted out :)
Dan, Please let me know the basic code to run macros on shared workbooks.
i have just google a code for auto save the shared workbook every 5 mins and after sharing enabled seems to be macro is missing from workbook.
hi +muthu kumaran I don't have the answer to your question, in general I would strongly suggest using Excel 2016's built-in Auto Save functionality instead of attempting to write a macro
hi dan,
if i have two sheets but different columns
for example
one sheet(amount, price, name)
sheet two(amount, date, note)
how cam i marge this two with macro
thanx
Heya +Vadim br -- merging two sheets with different headers is very doable. However, you have to decide how you would like them to be merged -- do you want to maintain all of the columns?
yes
Hey +Vadim brs! I tackled this challenge (where columns are out-of-order) here:
danwagner.co/how-to-combine-data-from-multiple-sheets-into-a-single-sheet-but-different-columns/
Here's a link to the video guide:
danwagner.co/screencast-combine-sheets-with-different-columns
Howdy +Vadim brs -- apologies, the last comment has the incorrect link. This should do:
Tutorial: danwagner.co/how-to-combine-data-with-different-columns-on-multiple-sheets-into-a-single-sheet/
RUclips: danwagner.co/screencast-combine-sheets-with-different-columns
Thanks!
Hi dan,
Daniel here what an amazing video to learn from.
I have an issue though the data inputs in my case are filtered after excluding some rows but all my input sheets are having same columns but then after filtering about 21 sheets my range in each sheets differs can please help me getting a workaround for this as I want to combine the filtered data to a summary sheet.
hi +cool.danny 007 -- you can adjust the rngSrc variable in this code to instead use the Offset logic defined in this tutorial on line 31: danwagner.co/how-to-delete-rows-with-range-autofilter/ except you do NOT want to delete, you want to copy :)
Hi Dan,
The code is very good. After combined in the import sheet, could there be a column that can show can show the initial data sheet name for references ?
i.e. New Column in the import sheet listed the sheet name of their initial source of the information.
hiya +Win Tong Lim -- yes, this is possible! You can use the technique on lines 93-119 in this tutorial to accomplish that: danwagner.co/how-to-combine-multiple-excel-workbooks-into-one-worksheet-with-vba/
Hi This is very helpful! I'm running into an issue where the macro is ignoring my last occupied column and only taking the column before the last one. The src ends on column X but the macro only copies through column W. Also, some sheets don't have data in column X as this is a variable column. HELP PLEASE!
Hey +Rochelle Felipe -- this script only calculates the last column once, so I'm guessing that the first sheet does NOT have data in column X (so it thinks the last column is always W). This tutorial handles situations like yours, where the columns may vary: danwagner.co/how-to-combine-data-with-different-columns-on-multiple-sheets-into-a-single-sheet/
How do you limit to only 4 columns even if you have more occupied columns?
Hey +Vikram Kanda if you only need 4 columns, I would suggest copying all the data over (like the tutorial) then deleting the columns you do not need at the end of the script
if on a given table there are empty cells throughout various columns/rows (and we want to keep it as it is), how would you overcome the issue of the code stopping as soon as it reaches the first empty cell?
hi +daringwrinchester -- the code as-written should find the very last occupied cells both in the X and Y directions, then copy the ENTIRE range, leaving your empty cells intact. if it is not doing that, something is majorly wrong! what happens when you run the code as-is?
It's only copying the first row of every sheet for me. Any ideas on what might be happening? I am trying to copy multiple tables to a master sheet.
Hey +Chris Poulos -- if the script is only copying the first row, that means lngSrcLastRow = LastOccupiedRowNum(wksSrc) is 1 which is very strange, I suggest adding break points and examining that variable as you loop through each Sheet to see why lngSrcLastRow is equal to 1 each time
@@DanWagnerco My apologies, it is copying the first column from each sheet.
Chris Poulos OK awesome, when only the first column is copied that probably means the first Sheet being examined is empty, so the last column variable is set to 1. Look for an empty Sheet in your file and remove it OR make sure you’re skipping that Sheet in the loop
@@DanWagnerco It worked! Thank you so much for the prompt response. Great code.
thank you sir this is what I'm looking for, I have question is what if there are several sheets that I don't want to combine
Hey @Rian Sutarsa great question! You can use an If statement to check the Sheet names and determine whether or not you want to combine that Sheet like I show in this tutorial: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
Hi Dan,
This is awesome! So helpful for a newbie. I was wondering if there is an easy way to specify the sheets I wanted rather than looping through all sheets. I have a workbook with 8 tabs or so but need to do this with just 4 of the tabs. Again thank you so much for this video.
Hey Jackson -- great question.
If you only want to combine 4 specific tabs (amongst many), you'll need to tweak line 22 in this macro (which is available here: gist.github.com/danwagnerco/040402917376969bf362).
Rather than skipping the "Import" sheet, which is accomplished on line 22 by If wksSrc.Name "Import" Then, you'll instead want to verify that the name matches one of your sheet names by using "=" and "or":
If wksSrc.Name = "SheetYouWantToImport1" Or _
wksSrc.Name = "SheetYouWantToImport2" Or _
wksSrc.Name = "SheetYouWantToImport3" Or _
wksSrc.Name = "SheetYouWantToImport4" Then
Worked perfectly. Thank you so much for the help!
Hi Dan. I've 'borrowed' your VBA with line 22 amended so that it only picks up nominated sheets and it works perfectly. With my very limited VBA knowledge how would I need to amend the VBA to (a) clear the records on the 'Import' sheet (except headers) every time I run the VBA, i.e. I don't want to append the data on the 'Import' sheet but start with a clean sheet every time the VBA runs? and (b) how can I nominate a different starting column on the 'Import sheet', i.e. my source sheets all have columns A to D but in my destination sheet ('Import') the data needs to go into columns H to K?
@@DanWagnerco Hi Dan! Thanks for sharing this code but I have changed the formula with (=) sign. However, its only copying the first column. what should be done next to copy the and paste the all columns in source_sheet?
@@javalearner4227 If the script is only copying a single column, that means it is probably starting on a blank Sheet and calculating that the last-occupied column is 1. I suggest removing the (probably) empty Sheet or skipping it -- thanks!
Hello Dan,I keep getting the message below: Run-Time Error '9':Subscript out of rangeVB highlights line 11:Set wksDst = ThisWorkbook.Worksheets("Import")not sure if anyone else got this error. It is clearly within range but not sure what is going on.
Found the issue... I was loading the code into PERSONAL.XLSB since I wanted to use into any open workbook but since the code is defined to one particular workbook I was getting the out of range message....
anyone encountering this same issue... if you want to use this code to any open workbook simply set the ("Import") to ("Sheet1") and make sure the entire code is in the PERSONAL.XLSB. and do not change the title of the sheet until after you're done running the macros.
if there's a better and easier way please post.
Heya +Bernardo Reyes -- awesome detective work!
Since you'd like this code to reside in your personal.xlsb, "ThisWorkbook" will no longer be the target of all the operations -- the source of the problem, as you accurately identified.
If you are 100% sure that you will only execute this code on active workbooks that need combining, you could change "ThisWorkbook" to "ActiveWorkbook" and make sure the destination Sheet name is set appropriately (since it probably will not be "Import").
Hope that helps!
Doesn't work me, copied your code nd gets stuck at "LastOccupiedRowNum", comes back with Sub or Function not Defined
Hi +Paul Haslam, are you absolutely certain you copied all the code? LastOccupiedRowNum is a function defined at the very bottom
Hello Dan, Superb Video, Thanks a lot for the same.
I would like to skip one more worksheet with name "Data"
How can I do that
Hi +Nilesh Kadu you can follow this tutorial to see how to skip certain Sheets but not others when combining data: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
Hi Dan, If I only want to copy the data start from sheet #3 to the last sheet (the # of last sheet is varied in each file)... what should I do? Thank you.
Hiya +Prelude Fugue, I'm sure this challenge can be handled, but I need to know a bit more about the problem at hand. Are the sheets named the same way, like "Sheet3", "Sheet4", "Sheet5", ... "SheetX"?
Hi Dan, thank you for your reply!! The name in those sheets are different... We created these workbook for tracking Department sales. so each department has its own tabs..... Also each workbook might have varied number of tabs depends on how many departments in that locations.. The only commend thing is... I only need to copy the data start from the 3rd sheets (count from left) since the first 2 sheets are not department sales........also the columns and columns heads(row1) in these Department sales sheets are exactly same... When I copy these department sales into a "summary sheet", I wish to copy the formula as well, so if I update any of these sheet, the summary sheet will also be updated..... Is these possible ??/ I am so new to Macro and totally lost
Hey +Prelude Fugue, totally possible and this situation is not that uncommon. It sounds like you need to skip the first 2 sheets, and they are pretty well-known... if you know the names of the 2 sheets, you can use the strategy in this tutorial: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
Code doesnt work- in debug shows that LastOccupiedRowNum is not defined
Hey +Harijs Osis -- LastOccupiedRowNum is a function defined at the bottom of the code in the tutorial, are you absolutely certain that you copied EVERYTHING? Here is a direct link, do you still get the error when you copy all the code (from line 1 through line 85)? danwagner.co/how-to-combine-data-from-multiple-sheets-into-a-single-sheet/
Hey Dan,
How do you paste values instead of past formulas? Thanks!!
NM figured it out. You change the copy destination to:
rngSrc.Copy
rngDst.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone
Hey Albert -- excellent q!
(Please pardon any typos as this comment is coming from a phone)
You'll need to tweak line 30 into two lines:
rngSrc.Copy
rngDst.PasteSpecial xlPasteValues
That should do the trick!
+Dan Wagner thanks dude
This video is great! How can I have it only pull in the data if column A has an "X" or "Y"?
hi +Brenson Saint Jean I would use the Range.AutoFilter method, this tutorial covers that method in the context of deleting rows (but it's the same concept): danwagner.co/how-to-delete-rows-with-range-autofilter/
Dell Dan! I have unstructured data (data in different column) in the multiple excel tabs and I want to merge the data in one sheet.
Hey +Vipin Sahu this tutorial explains how to combine data from multiple Sheets into a single Sheet even when the columns are out of order: danwagner.co/how-to-combine-data-with-different-columns-on-multiple-sheets-into-a-single-sheet/
I've applied this to my workbook and ran into a few small issues, but i'm stuck at 3). Can you help please?
1) Firstly, I wanted this script to only target sheet names containing "CDK" within my workbook
'Loop through all sheets
For Each wksSrc In ThisWorkbook.Worksheets
If wksSrc.Name Like "CDK*" Then
2) Secondly, I wanted the script to paste values the destination sheet only
'Store the source data then copy it to the destination range
With wksSrc
Set rngSrc = .Range(.Cells(2, 1), .Cells(lngSrcLastRow, lngLastCol))
rngSrc.Copy
rngDst.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
End With
3) Thirdly and most importantly, the script is only pasting column A of my source sheets to column A of my destination sheet. My source sheets contain 7 columns worth of data...
hi +Ashley Townsend -- for your first question, you want to use InStr to check for "CDK" in the Worksheet name. Here's an example: stackoverflow.com/a/15585089/3075291
hey +Ashley Townsend -- also for your first question, here's a tutorial I wrote on combining certain Sheets but not others: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
hello +Ashley Townsend -- this script calculates the last occupied column based on your "Import" Sheet, which I am assuming is empty in your case (meaning that the function returns a 1) but in this example, the "Import" Sheet already has headers, meaning that the last-occupied column calculates correctly. To fix this you should either add headers to your "Import" Sheet or calculate the last occupied column from the first Sheet that actually contains data
Thanks for the really quick replies...!
I failed to mention that i'm a VBA newb.
Both of my import sheets that I'm testing this on aren't empty, they both contain data from Columns A:G
Interesting +Ashley Townsend -- so what is the value of lngLastCol?
this is great, Dan.
How can i get this to skip different sheets?
Hiya +Marcus Kay -- this ought to help:
danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
Good luck and don't hesitate to holler at your boy if you have any additional questions,
Dan
it really does! Thanks again!
When I run the module, it only grabs info from the first column and nothing else, it gets each worksheet but only column 1?
Hi +Eamon McCourt -- that happens when your "Import" Sheet is left blank, forcing the lngLastCol value to 1. To fix this you'll either want to (1) drop the headers into your "Import" Sheet like I do in this example by hand or (2) modify the code to calculate the last-occupied column on the first data Sheet
Dan Wagner makes perfect sense now, for the life of me, I couldn’t figure it out.
Thank You!
Will this work when each sheet has extra columns. I want to look at copying all columns from multiple workbooks/sheets over wherever each column matches.
hi +Zach Clement no this tutorial assumes that all sheets have the same column order, this tutorial handles out-of-order and additional columns: danwagner.co/how-to-combine-data-with-different-columns-on-multiple-sheets-into-a-single-sheet/
Dan Wagner I am looking for a macro code that can copy columns that match based on the destination workbook. It is key that I paste this data to existing placeholder columns in my destination workbook . I also need the macro to loop and do this for several different worksheets that have differing column name combinations. Note: all of the columns of the columns I want copied over are in the destination workbook already.The unique thing is my destination file has a placeholder for all of the differing worksheet column name combinations. I cannot seem to find a macro anywhere on the internet that does this function. The macro would need to sense where data stops in the destination workbook so it can paste the next.
heya +Zach Clement -- fascinating and well-described! I have not written a tutorial on that exact case, but it can absolutely be done. I'm testing out a handful of packaged services that might be interesting to you, email me at dan@danwagner.co if you'd like to discuss further
hey Dan how do you allow for duplicates or does it automatically? and how do add tabs to skip? thanx
+DigitalErra Hiya DigitalErra -- in the code used for this video, duplicates are allowed. Do you need to remove duplicates? As far as skipping tabs goes, you can check names in the For Each wksSrc In ThisWorkbook.Worksheets loop. Is skipping tabs something you need to do often?
+Dan Wagner I would have charts and other data that will not need to be copied over. I would need columns A-M to be copied, and 31 tabs to represent everyday of the month all at various lengths. I could send you over the sheet that I will use daily.
Hi Dan! this was extraordinarily helpful. QQ - I have a LARGE workbook, over 40 tabs. I need to combine 3. How can I amend so that instead of saying EXCLUDE these 37 tabs, I can ask it to INCLUDE only these 3 named sheets. Thanks in advance.
'the names we know we DO NOT want to combine into ALPHA
For Each wks In ThisWorkbook.Worksheets
'If this sheet name is NOT one that we want to ignore,
'append the data to the ALPHA sheet
strName = UCase(wks.Name)
If strName "Sheet1" And _
strName "Sheet2" And _
strName "Sheet3" And _ ........ to Sheet 44
Hey +Shannon Ryan, in this case you will instead want to use strName = "WhateverName1" Or strName = "WhateverName2" Or strName = "WhateverName3". Thanks! -Dan
@@DanWagnerco thank you so much for taking the time to reply! This worked perfect :)
In my workbook, the macro stops at line Set wksDst.. with the error message Run-time error '9'; Subscript out of range.
I have named a blank sheet "Import" Any idea what is wrong?
Thank you.
Hey +robert cline -- is the "Import" worksheet inside the Workbook that you are running the macro from? That's what ThisWorkbook.Worksheets("Import") is assigning
Yes. The "import" worksheet is inside of the same workbook with all other sheets.
..and the vba code.
That's a puzzler +robert cline, I can't troubleshoot further without looking at the file. Can you send it to me? My email is at the bottom of that article
I have 18 columns. Column A to R. On some sheets, there are two blank columns then a pivot table in column/row T1 on the same sheet. Could that be the issue?
Since you responded to Rochelle that the code only computes the columns once, I places a sheet with only columns A-R and no other data as the first sheet after "Import" sheet.
I am very new to VB and i don't know much. But using your video i tried combining data into one. But i got a message saying sub or function not defined. Please help me solve the issue
Hiya +latesh shetty -- not to worry, I think I know what the problem is.
Did you definitely copy all of the code from the example write-up in the linked tutorial? Here is a link to that code direct:
gist.github.com/danwagnerco/040402917376969bf362
It is possible that you missed one or both of the functions defined and implemented at the bottom ("LastOccupiedRowNum" and / or "LastOccupiedColNum").
Thank you so much Dan and you were right i missed couple of lines. Let me try using this
Hi Dan, How can I link the import sheet to the source? Can the new inserted data be shown in the import sheet? How can i do that too? Please help!
hi +Man Ying Chu -- I am afraid I do not understand your questions. Can you clarify?
Can I synchronize the import sheet and source sheet? which means I want to keep inputing new data into source sheet and I want the new data shows in the import sheet too
Hi Dan! Thanks for this! Its super helpful! 2 questions. How can I set it up so that's its checking every sheet except for 1 sheet?How can I restrict the range in each sheet that its looking to? I want it to pull data from a smaller range of rows.
Hiya +Lauren Henderson!
(1) Here's a tutorial I wrote about combining certain sheets, but not others: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
Of course, if you'd prefer to go straight to the video, it's here: ruclips.net/video/ZRKk2XHwUX4/видео.html
(2) Can you tell me more about the range on each sheet? Is it always the same?
Hi Dan,
The range is always the same. I have 10 or so sheets with 2 separate tables that each client fills out, and I need to extract the data from only one table onto a summary sheet,
Hey +Lauren Henderson -- cool, that is very doable!
You'll want to change line 29 to be something like:
Set rngSrc = .Range("A1:G20")
where your well-defined Range can be any start / stop cells (like "B5:C10", "Z1:AA3", etc.)
Awesome! That helps a ton.
What is the code I would use in order to get the macro to skip all rows from the table that are blank.
I want it to extract any row of data that has at least one cell filled in from the table. And I want it to skip any rows that have all empty cells.
Hey +Lauren Henderson -- that's a great question, and something I hope to answer soon with a new article and tutorial!
Hello sir
I am Tushar Sharma from Kanpur
I want create a master of ms excel. I have workbook having 500 sheet.
I want to prepare a list of headings with thier values
I am unable to use VBA coding
By cut copy paste its hue stuff and time taking.
By dragging sheet no. Not increasing except cell no increased.
Example : =sheet1! B1
By dragging :=sheet1! B2
I want : =sheet2! B1
So please help me
hi +tushar sharma i'm afraid i do not understand your question...
Hello sir,
Please tell me how to combine the sheet 1 datas from multiple files into single excel sheet of master workbook.
Hi +kanmani chinnadurai -- please follow this tutorial to combine Sheets from multiple Workbooks into a single file: danwagner.co/how-to-combine-multiple-excel-workbooks-into-one-worksheet-with-vba/
Dan,
I emailed you, but thought I would ask my question here too. What if the only information I want to copy is in cells A4:V4 on thirty different sheets?
Yeah, I have same problems. Hope Dan could help on that.
Hi there, I have the same problem.
this can be resolve by checking this line 29 where "Set rngSrc = .Range(.Cells(2, 1), .Cells(lngSrcLastRow, lngLastCol))"
The above has set the range base on the predefined rules i.e cells that contain data.
But to set the copy area as A4:V4, it would be read as "Set rngSrc = .Range(.Cells(4, 1), .Cells(4,22))"
Hope this help.
Hi, I have a similar problem is there a way of moving the copy range one column across example C21:F40 and excluded two sheets instead of just one?
hi +Alex Allen this tutorial explains how to skip more than one Sheet: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
heya +Alex Allen you can modify lines 28-31 to just copy a specific Range (like C21:F40) rather than calculating the last row each time
How to get the vba code sir
Hi +KRISHNA JAGADEESH you can get the VBA code from the first link in the description which points to the tutorial here: danwagner.co/how-to-combine-data-from-multiple-sheets-into-a-single-sheet/
Hi how do skip few sheets. I have some 13 sheets i need to copy data from only 10 sheets.
hi +muthu kumaran I wrote a tutorial on doing that here: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
Thank you Dan
Hi! is there a way to insert some line of code to insert index match or static commands? for instance i would want to insert a date in column A based on the date on that worksheet. Thanks!
Hey +Chrissy Lynch -- I'm not sure I understand your question. Do you want to add columns to the resulting combined data? Or into the source data on the individual sheets?
Dan Wagner I want to add data to the combined results tab. so for instance I would insert a date in column a and the word lunch in column c and column b would be what was copied from the individual tab. I hope that makes more sense. thank you!
Great work.... is it possible to skip a sheet from the excel workbook
hey +mithun nair -- yes, you can skip sheets by following the tutorial here: danwagner.co/how-to-combine-data-from-certain-sheets-but-not-others-into-a-single-sheet/
Dan Wagner - ha thanks for such a quick reply... i am very much new to this .. let me go through it and will get back to you if i had any doubt ... thanks and good luck
all good +mithun nair, that tutorial should get you where you need to go!
Ha Dan Wagner it worked for me in a different way... I used ur 2nd macro it started skipping unwanted sheets as expected but not combining all the sheets to one excel.. then I pick up 'strName' into old macro and it worked successfully well but, a small issue is that it copy the header again which is getting duplicated in the master sheet... I added a 'selection.delete' into it just before 'MsgBox' and it actually not the right approach but it's working as of now. But let me know if we can stop this by modifying the macro
Now one more issue is that, if I run the macro again or I hit it twice accedently, it will copy the data from other sheets again into master sheet creating doubling all the data. And create duplicates .... Will u be able to help me on this.. So that there will be some mechanism to prevent the data from duplicating
Hi +mithun nair -- I'm not sure exactly why you are getting multiple headers as I cannot see your code, but in general you can avoid header copying in a loop by using a strategy like lines 60-62 from this link: gist.github.com/danwagnerco/a5be4bd8e4843533a096f9ab91bcb3ed#file-combinemanyworkbooksintooneworksheet-vb
Hi Dan,
I copied your code but for some reasons, it only added into 1 col with 2 rooms :/
Thanks!
Hi +Tra Pham -- if it only copies 1 column, the issue is almost certainly that lngLastCol (variable) is not getting correctly calculated. Do you need to skip certain worksheets, or is there perhaps a hidden worksheet that is empty?
Thanks A lot Dan, really helped me a lot.
hey +CFA Vinay Chhaparia happy to help!
thank you so much. It worked perfectly.
Happy to hear it!
thanks so much for VBA script :)
Wowoo! this worked like a rocket. Thank you so much
Can u share the VBA CODE pls
I want to copy all the data from several worksheets to a master worksheet. Let's say an individual worksheet will have data in only 3 columns name, possible points and total points. Another worksheet will have the same column names and many more. I want all these information no merged but displayed individually but on the same master worksheet. So the master sheet will have all teams separately displaying all the information next to each other to compare. 1st showing the first worksheet and next the 2nd worksheet data. I hope it makes sense. I have found videos on how to merge, consolidate, get ranges, etc... but I haven't found anything to do what I'm trying to do. If it's done automatically it would be best. A different person will be adding information to each worksheet and all the data from all the worksheets should be automatically updated to the main worksheet to see all groups next to each other and compare. This is for a competition. is VBA the only way to accomplish this?
Hey +Miguel Angel Torres Espinoza -- you can use this strategy to copy all columns from all sheets, one on top of the other, with columns matched where appropriate:
Tutorial: danwagner.co/how-to-combine-data-with-different-columns-on-multiple-sheets-into-a-single-sheet/
Screencast on RUclips: danwagner.co/screencast-combine-sheets-with-different-columns
thanks, I will try it. Although, I think it would be more efficient to add them next to each other because each worksheet only has 3 columns and i would be able to see all the teams without having to scroll down. I could just use one worksheet are and see all the teams at the same time. is that possible?
Hey +Miguel Angel Torres Espinoza -- I thought all the sheets had different columns?
Each worksheet has 3 columns: name, possible points and total points. I'm using column A for numbers 1-100 and column B,C, and D for the data. I want to be able to displayed the data from each team on one main worksheet next to each other. I could use columns B,C,D for one team and leave a space and next F,G,H for second team, etc... up to 20 teams so I can compare scores. Is it possible to transfer data from columns B,C,D to other letters?
Example: team 1 columns A,B,C. Team 2 E,F,G team 3 H,I,J, etc...
Does it make sense?
Hi +Dan Wagner. Thanks for the tutorial but it combines the data. I don't need to combine the data. I just need to display the data from all worksheets next to each other to compare them. I want to be able to see all teams together from left to right but not combined. someone will be inputing data in every single worksheet. All the data from all teams "single worksheets with only 3 columns" should appear all together on the main worksheet as mentioned before. Not combined, not merged, not consolidated. Just displayed exactly as they are next to each other to compare them. I tried creating a link but once I uploaded to dropbox there's a message that says that the browsers don't support links and are disables. It only works in my mac. I'm trying to do somehting that will work permanently even if i share the file in dropbox to be update by other people and update automatically on the main worksheet will all the results from all teams to get the scores for all the teams. I hope it makes sense.
so helpful!
Go helpful, thank you very much
Really appreciate the kind words +Li Su!
Hi Dan,
I sent an email to you for my special case. Could you kindly help on that?
Thanks.
Li
u saved me . Thanks
great +Amal Radwan, happy to help!
great, Thanks
Doesn't work me, copied your code and gets stuck at "LastOccupiedRowNum", comes back with Sub or Function not Defined
HI +Paul Haslam, are you absolutely certain that you copied all of the code? That error occurs when LastOccupiedRowNum, which is a function defined at the very bottom of the code, is not copied over
@@DanWagnerco Yes, copied from your link, I even slimmed down the example to only copy sheets of data and keep the names of the sheets as one word
Set rngSrc = .Range(.Cells(2, 1), .Cells(lngSrcLastRow, lngLastCol))
rngSrc.Copy Destination:=rngDst - this piece of code looks at little weird to me, but I am no expert. commas followed by dots?