Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

Combine Data from Multiple Worksheets and Workbooks in Excel










Hi

I have many work sheets in the work book and need to on a number of
occasions create a single worksheet by appending one work sheet after the
other. Is it possible to do this with a function, or command? instead of
doing it manually by copying each of the worksheets to a single work sheet.

thanking you in advance

regards

norman

--
Message posted via http://www.officekb.com



Example: 2 excel files from which all the data are in column A. But the values in column A are strings separate by a coma.
First thing I have to do is to merge the column A from excel file #2 to column A excel file #1 by their corresponding rows. Ex: excel file #2 row 1 to be combined to excel file #1 row1 in just one string. Once this is done I have to find if there are duplicates from one row to the others among different columns. There can be duplicates on the same row (horizontally) but not vertically.

Here are my questions: How to combine 2 worksheets and matching the row without creating empty cells? Once this is done how to find (highlighted or identity) duplicates form one row to the other among different strings without deleting the duplicates?


Thanks for any help.... hoping to get some...


Hi peeps

I want to combine data from several worksheets into one worksheet.

For example, I have data in Sheet1 (Columns A,B,C), data in Sheet2 (Columns A,B,C), data in Sheet3 (Columns A,B,C) all with varying amounts of rows. (All the rows contain text data).

I need to combine all of the data from the 3 sheets into a single sheet, Sheet4 (Columns A,B,C), eliminating the empty rows.

I've been looking into this for a while, and can't find anything that really helps. Anyone got any pointers of what to look into?

Any help will be beautiful.

Cheers



I am trying to make a master list from a number of worksheets on a 2003 excel spreadsheet.
I am a teacher and our school is trying to increase our student's completion rate of homework. In order to do this we have created a spreadsheet to report students who have not turned in homework.

I have assigned each teacher a worksheet with in the spreadsheet. I want to take their data from various worksheets and compile it into on master list.

For example, I want to take Teacher A's A2, A3, A4...cells and place them on a list with Teacher B's A2, A3, A4... cells.

How would I do this?

I have attached a sample excel sheet I am trying to make.


Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
MyPath = "C:\MyPath" ' change to suit
Set wbDst = Workbooks.Add(xlWBATWorksheet)
strFilename = Dir(MyPath & "\*.xls", vbNormal)

If Len(strFilename) = 0 Then Exit Sub

Do Until strFilename = ""

Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)

Set wsSrc = wbSrc.Worksheets(1)

wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)

wbSrc.Close False

strFilename = Dir()

Loop
wbDst.Worksheets(1).Delete

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


I found this code in a similar (2 year old) post by someone in this forum.
Now I think nobody will post a reply to my question in that thread, so I am starting a new one.
I am looking for a code which will move all the sheets in the directory to one single workbook and the source workbook should remain intact.
(Instead of this code I need a code which will copy all the workbooks not just one, and the code should not delete the source file or any of its contents)
I am below Zero when it comes to VBA, so please help me out.


Hi!


I have three set of excel sheets from three different sources of data. Each sheet has unique ID number. I would like to consolidate the data as one. For instance, I would like to know how many times a unique ID number is on the different excel sheets.


I have two worksheets with a column of part numbers. I am trying to combine them in a new worksheet and remove duplicates. Any recommendations?


I have being trying to combine different workbooks (with multiple tabs) into a single workbook (Master Workbook) with multiple tabs (worksheets) but not happening. Any help


I have read all post regarding this and still can't find a simple solution. I have 9 workbooks open and each workbook has 1 worksheet with various sheet names. I am trying to combine them all into 1 new workbook with the sheet names remaining the same.

Basically, I want to do a "Move Sheet" to New Workbook and then close the old workbook but I want to automate it to do all 9 files. Hope this makes sense.

In a nutshell, I want to merge 9 open workbooks into 1. Thanks.


Hi All,

You helped me a while ago putting together a macro in the thread, "Combining Multiple Cells in Multiple Worksheets in Multiple Workbooks into one Table", which, as the thread name indicates, collected specific cells from multiple workbooks and put them into one workbook for me.

I know have a similar issue that expands upon this idea, yet seems to me be slightly more difficult.

Whereas originally, the cells being pulled each time where the same in each workbook, i now need to create a macro that will look into a workbook and pull the names of multiple sheets for use as headings, and pull cells from an array that will be different in size for each workbook.

This is quite tricky to explain in words and i wish i could show you, but i don't know how.

Maybe first things first, how can i change the original macro, listed below, to look at the names of the worksheets and pull them as headings for my report?

Please tell me anything you need that will make this easier for you! I will do my best to accomodate.

I really appreciate any help you can give me!

Sub test()
Dim myDir As String, fn As String, temp As String, ref As String
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = True Then
myDir = .SelectedItems(1) & "\"
Else
Exit Sub
End If
End With
n = 2
fn = Dir(myDir & "*.xls*")
Do While fn ""
If fn ThisWorkbook.Name Then
n = n + 1: temp = "='" & myDir & "[" & fn & "]"
With ThisWorkbook.Sheets("sheet1") '

Good morning from usually sunny Las Vegas, it was a beautiful way to wake up. I am having a small problem that is likely very easy to fix, I must be having a brain cramp. I want to combine all the fields from 2 worksheets to a 3rd worksheet. I have labelled the worksheets -- I am sure this is grade school stuff for many of you, I must be making one critical mistake.

Any guidance will be appreciated as I am just spinning my wheels so far.

Thanks in advance!

combine two worksheets with one common field.xlsm

I am a beginner to Excel and VBA, can somebody show me a few lines of scripts and instructions how to use VBA scripts to combine multiple Excel xls Files (which contain single worksheet) into a single Excel file of multiple worksheets?

Can somebody also suggest a good book with examples I can start to learn to solve these kinds of problems?

Thanks very much

Excel_beginner




Hi Guys

I currently have multiple excel workbooks (11 workbooks), with multiple spreadsheets (approximately 12 sheets per). Each workbook is derived from a common template. I would like to combine these workbooks into a master document, i.e. one workbook with 12 sheets, with the corresponding information from each exel spreadsheet from each workbook, found on the coressponding sheet in the master document. Each document also has a prompt which asks to be updated when opening it. it is not necessary to update the document. Can anyone please assist me. Thanks.



Post amended following feedback

Hello all

I have been dipping in and out of this forum for a while and generally find the answers I need from other posts. However I cant find a way to solve this issue.

I am collating a large amount of data for a payroll system change over. This involves requesting 6 locations to complete a data collection exercise each week. The data sheet is identical for each location with a separate tab for each week they are collating for.

The layout of the worksheet is as follows

Cells A to G contain employee details (Name, Job Code, Payroll Number, Location of work etc)
Cell H is unique to each worksheet and indicates the week commencing date
Cells I to Y contain the payroll data I am asking sites to complete.

I have several processes I need to go through in order to get this data from a format that is easy for the sites to complete to somthing i can migrate into the payroll system.

Step 1
I need to combine all the data across the multiple worksheets and workbooks into 1 worksheet

I have found a macro to combine multiple worksheets into 1 within the same workbook (given below). What I need however is to be able to combine the worksheets across all 6 workbooks into 1 worksheet.

The macro I am using currently is

Sub ConsolidateSheets()
'JBeaucaire (6/26/2009)
'Merge all sheets in a workbook into one summary sheet (stacked)
Dim cs As Worksheet, ws As Worksheet, LR As Long, NR As Long
Application.ScreenUpdating = False

If Not SheetExists("Consolidate") Then _
Worksheets.Add(Befo =Sheets(1)).Name = "Consolidate"

Set cs = Sheets("Consolidate")
cs.Cells.Clear
Sheets(2).Rows(1).Copy cs.Range("A1")

NR = 2

For Each ws In Worksheets
If ws.Name <> "Consolidate" Then
ws.Activate
LR = Range("A1").SpecialCells(xlCellTypeLastCell).Row
Range("A2:AA" & LR).Copy
cs.Range("A" & NR).PasteSpecial xlPasteValues
Application.CutCopyMode = False
NR = cs.Range("A" & Rows.Count).End(xlUp).Row + 1
End If
Next ws

cs.Activate
Columns("A:AA").AutoFit
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Public Function SheetExists(SName As String, Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

If I have to manually copy and paste each of these combined worksheets into 1 new workbook and then rerun the above Macro I can live with this but if anyone can adjust the above macro to work across multiple workbooks within the same Folder that would save alot of time.

Step 2

Once this is done I then need to take all the data in columns I to Y and put them in 1 column. Not combining them but stacking them at the same time I need to keep the relevant Employee details for each payment.

As an example of this if the spreadsheet was

A1 B1 C1 D1 E1 F1
A2 B2 C2 D2 E2 F2
A3 B3 C3 D3 E3 F3
A4 B4 C4 D4 E4 F4
A5 B5 C5 D5 E5 F5
A6 B6 C6 D6 E6 F6
A7 B7 C7 D7 E7 F7

with columns A to C being the employee data then I need it to become

A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3
A4 B4 C4 D4
A5 B5 C5 D5
A6 B6 C6 D6
A7 B7 C7 D7
A1 B1 C1 E1
A2 B2 C2 E2
A3 B3 C3 E3
A4 B4 C4 E4
A5 B5 C5 E5
A6 B6 C6 E6
A7 B7 C7 E7
A1 B1 C1 F1
A2 B2 C2 F2
A3 B3 C3 F3
A4 B4 C4 F4
A5 B5 C5 F5
A6 B6 C6 F6
A7 B7 C7 F7

I am thinking that a possible solution to this stage could be to separate my data so cells I to Y are extracted into separate worksheets with Cells A to H and then re run the above macro therefore re combining the data into 1 worksheet in the format I need but any ideas how to do this would be gratefully received.

Apologies if the above description is not clear enough please let me know if you need more information.

I have attached a sample file please note the actual file will have more tabs (roughly 24) and there are 6 of them each with different employee data.

Thankyou

Richard



Hello,

Can someone please tell me if there is an easy way to take separate workbooks: workbook1, workbook2, workbook3 and combine them into one workbook that has multiple worksheets (WorkbookAll that has worksheet1, worksheet2, worksheet3).

Thank you.

MAB