Excel: Merging specific worksheets from different workbooks into a master file

%3CLINGO-SUB%20id%3D%22lingo-sub-3003263%22%20slang%3D%22en-US%22%3EExcel%3A%20Merging%20specific%20worksheets%20from%20different%20workbooks%20into%20a%20master%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3003263%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%20all%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI'm%20just%20starting%20with%20VBA%2C%20so%20I%20would%20like%20some%20help%20with%20this%20task%20at%20hand.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20four%20Excel%20files%20named%20%22C%20Tier%204%22%2C%20%22L%20Tier%204%22%2C%20%22S%20Tier%204%22%2C%20and%20%22O%20Tier%204%22.%20Within%20each%20of%20these%20workbooks%2C%20there's%20a%20tab%20called%20%22Data%22%20containing%20information%20that%20I%20need%20to%20combine%20and%20extract%20to%20a%20master%20file%20(another%20file).%20Unfortunately%2C%20one%20of%20the%20columns%20in%20%22O%20Tier%204%22%20is%20incorrectly%20placed%2C%20so%20I%20need%20to%20make%20sure%20the%20workbook%20columns%20match%20the%20name%20of%20the%20master%20file%20columns.%20Also%2C%20since%20I%20already%20have%20the%20headers%20on%20the%20master%20file%2C%20I%20would%20have%20to%20combine%20the%20data%20starting%20on%20cell%20A4.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EHow%20would%20I%20go%20about%20creating%20a%20button%20with%20VBA%20code%20to%20allow%20me%20to%20do%20that%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThanks%20in%20advance%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EXD%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3003263%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3005159%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20Merging%20specific%20worksheets%20from%20different%20workbooks%20into%20a%20master%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3005159%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1228997%22%20target%3D%22_blank%22%3E%40XDAnalyst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20would%20like%20to%20propose%20you%20modern%20solution%20using%20Power%20Query%2C%20that%20is%20much%20simpler%20that%20VBA%3C%2FP%3E%0A%3CP%3EYou%20can%20go%20to%20datatab%20%26gt%3B%20get%20%26amp%3B%20transform%20%26gt%3B%20folder%20and%20connect%20to%20the%20path%3C%2FP%3E%0A%3CP%3Ethis%20link%20will%20be%20helpful%20for%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fyoutu.be%2FwIFXqd0m4VY%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fyoutu.be%2FwIFXqd0m4VY%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20Microsoft%26nbsp%3BMVP%2C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%20%26amp%3B%20like%20it%20to%20help%20the%20other%20members%20find%20it%20more.%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi all,

I'm just starting with VBA, so I would like some help with this task at hand.

I have four Excel files named "C Tier 4", "L Tier 4", "S Tier 4", and "O Tier 4". Within each of these workbooks, there's a tab called "Data" containing information that I need to combine and extract to a master file (another file). Unfortunately, one of the columns in "O Tier 4" is incorrectly placed, so I need to make sure the workbook columns match the name of the master file columns. Also, since I already have the headers on the master file, I would have to combine the data starting on cell A4.

How would I go about creating a button with VBA code to allow me to do that?

Thanks in advance,

XD

1 Reply

Hi @XDAnalyst 

 

I would like to propose you modern solution using Power Query, that is much simpler that VBA

You can go to datatab > get & transform > folder and connect to the path

this link will be helpful for you https://youtu.be/wIFXqd0m4VY

 

 

Regards, Faraz Shaikh | Microsoft MVP, MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.