Free Microsoft Excel 2013 Quick Reference

Merge multiple workbooks Results

Hi,

Need your help on this.

I have around 11 circle files stored at the following path: C:BhartiMAPACirclewiseFY12-13Circle wise MAPA_Apr'12

Each File contain performance KPI's of each circle. These performance KPI's are captured in seperate sheets in the file. i.e Finance KPI's are captured in the sheet named "Finance KPI's", Network KPI's are captured in a sheet named "Network KPI's", Revenue KPI's in "Revenue KPI's" sheet. and like this the file has around 11-12 different KPI's sheets for that particular circle.

What I am looking is to consolidate finance KPI's sheet of each circle (11 circles) into a single workbook. likewise, network kpi's sheet of each circle into a single workbook and so on for each 11-12 different KPI's sheet ..intent is to compare the all circles KPI's in a single workbook. Right now, I have to open 11 workbook of each circle and then do the copy paste and thereon analysis/comparison...

Look forward to your response on this at earliest.

Hi Gurus,

I am building a manufacturing scheduler that will need to be formated as per some values. There are two timescales: 07:00-19:00 (day shift) and 19:00-07:00 (night shift). One step (column) is 10 minutes. Criteria are:
Product codeNo. of batchesColourRuntimeStart time
So lets say product1 (6 batches, colour green) starts at 07:00, runs for 80 minutes. What I need is to merge the range in a timescale starting from 07:00 + 80 minutes. In the merge cell I need to see product code "x" No. of batches, example: product1 x 6. Apply boarder and colour the merged cell in green.

Is it something that VBA could handle?

See attached sample workbook. I have set up a table to work with and the how results are expected to look like.

Any help is welcome.

I've been searching for about an hour now and have yet to find the solution to my problem.

I have 15 workbooks, i.e. Inventory 1, Inventory 2, Inventory 3.

In each of these workbooks there are 5 sheets, Desktops, Laptops, Printers, Monitors, Hubs.

On each sheet, the categories are applicable to the inventory.

Essentially, these 15 workbooks are identical in format, with different data.

I would like to merge all of the workbooks either into one workbook with 5 sheets that has all of the data from all of the workbooks.. or an Access database with all of the data from all of the workbooks. These are workbooks on Sharepoint that are constantly changing so copy/pastespecial does not apply here. Someone help me, please.

I have a workbook that contains 2 sheets, one sheet has #'s 1 thru 800 in
column A (there is only one instance of each # in this sheet). Then I have
another sheet that contains #'s 1 thru 800, but there are multiple instances
of each # (ie, 10 rows with the value = 1). I want to basically merge the
data from the "one shot row" into the sheet that has the "multiple instance
row". It is OK if the "One shot rows" show up each time on the "multiple
instance rows". Thanks to all.

Hopefully someone here can help, or at least give me a starting point, as I'm a bit lost as to how to do this.

I have 2 workbooks, each with 9 sheets. I need to merge the 2 workbooks (I can figure that bit out myself - I'll do it manually if need be).

The problem is then that there are likely to be duplicate entries across all 9 sheets (i.e. an entry on sheet 1 could have duplicates on sheets 3 and 5).

What I need to do is find these duplicates, and then move them so that they are under the original entry (and also delete them from where they originally sit)

Hope this makes sense, and that someone can help.

Ok, so this is the situation:

I need multiple people (3-5) entering data into a single workbook. The spreadsheets in it aren't anything complicated, each person enters new information when they need to, each entry comprising a row. It might as well be a table in Word.

So far, I've just been copying and pasting the entries into a master workbook to merge them, because the workbook sharing function has a problem. The problem is the conflict resolution doesn't provide a way to accept BOTH rows. So, if 2 people write a new entry onto the same line, I have to choose one or the other.

Is there a way to resolve the conflict by adding an extra row each time and keeping both entries, rather than allowing only one? I've thought of having a different view for each data puncher, but the monkeys would almost certainly stray into each other's views.

(And yes, I know this would all be easier with access, but we don't have access... some weird licensing shenanigans).

Here I have a workbook containing 7968 lines and 4 rows, with the first line as header line. What I want to achieve is to split this workbook into 80 individual workbooks, each comprised of 100 lines plus the header line in the first line and named as 01.xls, 02.xls, 03.xls, ..., 79.xls, 80.xls.

I'll distribule the individual workbooks to others. After they fill in the blanks, I will merge them back into one.

How to do it?

Thanks

Hello all,

After reviewing the forum I have found code that I can modify to consolidate sheets within a given folder, although it will select all Excel files.

I have also located code that can multi-select worksheets and open them. I just need to merge the two but do not know how.

I'd like to select multiple worksheets in a dialog box (GetOpenFileName), etc, then consolidate them to my "Consolidated" worksheet in the active workbook.

The code I found is as follows:

Open multiple files:

Sub OpenMultipleFiles()
Dim fn As Variant, i As Integer
fn = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select One Or More Files To Open", , True)
If TypeName(fn) = "Boolean" Then Exit Sub
For i = 1 To UBound(fn)
Debug.Print "Selected file #" & i & ": " & fn(i)


'put your code to manipulate the text here

Next i

End Sub
Consolidate sheets in a given directory:

Sub CollateReportFromFiles()
'Open all .XLS in specific folder (2007 compatible)
Dim strFileName As String, strPath As String, MyVal As String
Dim wbkOld As Workbook, wbkNew As Workbook, ws As Worksheet
    
Application.EnableEvents = False
Application.DisplayAlerts = False
    
Set wbkNew = ThisWorkbook
strPath = "C:Documents and Settings"
strFileName = Dir(strPath & "*.xls")
wbkNew.Activate
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp"

    For Each ws In Worksheets
        If ws.Name <> "Temp" Then ws.Delete
    Next ws

ActiveSheet.Name = "Final"

    Do While Len(strFileName) > 0
        Set wbkOld = Workbooks.Open(strPath & strFileName)
        MyVal = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
        wbkOld.Sheets(MyVal & ".xdo").Activate
        'Copy After:=wbkNew.Sheets(Sheets.Count)
        ActiveSheet.Copy After:=wbkNew.Sheets(wbkNew.Sheets.Count)
        strFileName = Dir
        wbkOld.Close False
    Loop
    
wbkNew.Sheets("Final").Delete
Application.DisplayAlerts = False
Application.EnableEvents = True
End Sub
Where am I to start on this one?

Thank you for your help!

I have spent the last 2 days searching in vain for the proper coding to bring together multiple files into a single worksheet. I am attaching the spreadsheets to this post. All passwords are "MRITAVEHO". The parent file is "MRI TAV FINAL COPY.xls" which I have pared down to an appropriate size by including only the pages I need and renamed "MRI TAV FINAL COPY (consolidation data only).xls. When the users use the file they save the file to a new name prior to using. After completing the worksheets they then finalize the file (adding the password which they do not know which has the effect of making it read only for them) under a new name. These files are posted into our sharepoint site. At this point our administrator takes over using "MRI administrator.xls" and the form which starts when it opens. The files are then merged with another file which keeps track of the inspectors and inspections and merges with a word document to generate an executive summary cover letter. This is all working quite well.

Now I have the task of trying to merge data to make meaningful sense out of it. I have added a page to the original file entitled "consolidation data" which collates all of the required data to a single worksheet within each workbook. I then expiramented with the consolidate and sum functions to pull data into the MRI Administrator.xls which is linked to graphs which provide the snapshot image we need. For me to do this is really not a problem, but for our administrator it would be impossible. We currently use "MRI TAV FINAL FORMAT.xls" to inspect ships every 18 months. The resulting *.xls files will thus have about 50 different names residing in the same directory yet having the exact same structure for the worksheet "consolidation data". Over time the data is not deleted, but new files are added. This meaning that in the next 6 years the files will grow from about 50 to 200. There will be much value added for the organization if we can strategically select files to add to the MRI Administrator.xls based upon user input (i.e. the administrator can select only files during specific dates or on specific types of ships) and then save the sheet with a new name which will preserve the data and associated graphs. (As I type this I am thinking that the best route would be to utilize an input form which can segregate out data based upon selected criteria - but that can come after I figure out the code!).

I have expiramented with macros trying to figure this out to no avail.

Anyone with experience in this area that could and is willing to help would be greatly appreciated.

Thanks, Rob

Hello!

I have the following "problem"

Scenario:
- Persons are working in seperate rooms meeting with their clients.
- This information(Time taken for visit etc) needs to be recorded.

I made the following plan:
- They open the excel workbook. They get a sheet called FillIn.
They fill in their name/clientname/arrivaltime (done by clicking a button)/endtime.
- They press Send.

Old
Now I used to have 1 big database in excel. But when sharing workbooks...this isnt working.
Lets assume Person A,B,C have the excel workbook open.
Person A adds a row in the database by pressing send (with the info). Yet Person B/C only see this change IF person A saves his file AND Person B and C re-open their file. If not, they save data over each other...major trouble.

Current
So I decided that each person (there are 4 , but will maybe become more later) has his own database. They fill in in what room they are "1,2,3,4" and that sends their information to database 1,2,3,4 respectively. This prevents data-overwrites from sharing-workbooks because they each send their data to seperate sheets(databases).

These databases then get merged to a big database, that data gets pivottabled so information can be summarized easily.

-----
Problems
1. I find creating these 4 databases and then merging them a bit dumb. I bet theres a better solution (and one thats more error proof).
2. If more people want to use the file...it cant happen unless you make another database specially for that person.
3. Im wondering how other work environments do this? Multiple people working in 1 database in a workbook.
The way I see it excel really has poor sharing-workbooks. You cant even see what others updated unless unless you re-open the file. Is this a correct view? Or is there tons of stuff in VBA/excel to make it work more optimal?

I did the send button etc with VisualBasic. This is my first time working with it and I only started learning excel 3 weeks ago.

Thoughts
What I was thinking myself...I dont know if its possible. What if I let them open the FillIn file in excel.
Then let the Send button send the information in the variables in VBA to a Microsoft Acces database. That database isnt shared and this allows it to be 1 big database. Doesnt matter how many people use the excel file then.
I could then import the microsoft acces database in another workbook and pivottable that data.

Im very interested in what you think about my problems, and the best way to make this scenario work.

Thanks in advance.

Cheers,

Chocomel

http://www.excelforum.com/images/smilies/confused.gifI have two versions of the same document that I need to merge. They are not set up as "Shared Workbooks", but they/it needs to be. I set one up to be shared, so it was no problem to do that. Now I just need to merge the two. This is a large, important document and I need to get it up and running soon. HELP!!

I have an excel workbook that will be distributed without any data to
multiple parties. After the various parties have entered their data (all of
which are dollar figures), the workbooks will be submitted via email back to
me. All of the workbooks will be formatted exactly the same and contain the
same layout.

My question: is there a way to merge the data in these workbooks to create
a summary of all the data on a new worksheet?

I have a workbook that I've shared and distributed to several people in order
for them to input data, and so that we can all merge our files as necessary.
However, the file is coming up on some computers as read-only, despite not
having that checked in the save options. Additionally, the box is checked to
allow multiple users to edit it simultaneously (although we aren't actually
using a network, thus each file is worked on seperately on each individuals
computer).

What do you all think?

I have been trying for weeks to try and get this working. A few have tried and Im not sure if it is because it is difficult or I am not explaining it well enough. Anyway, here goes...

I have multiple (13) client Workbooks called "Stats [Name].xls". These each have 4 Worksheets. Sheet1 is hidden (to populate lists in the 3 visible sheets) and there are 3 sheets which (may) contain data. They are named POC, ISS and ECS. All have identical formats and will be updated by users monthly then emailed to one person.

That one person then needs to compile all the data from these Workbooks into a Master called "Stats.xls". Now this is where it gets difficult...

The Master has the same format as the clients i.e. 4 sheets, the first hidden and 3 named POC, ISS, ECS. So the data from each Client sheet must copy to the appropriate sheet in the Master e.g. POC to POC etc without overwriting or affecting the list formatting in any books.

For example:
Client Book1 has 3 rows on the sheet POC
Client Book2 has 4 rows on the sheet POC
Client Book3 has 0 rows on the sheet POC

7 rows copy to the Master sheet POC

The cells that potentially contain data are A4:Q

A suggestion has been to have a button on a fifth sheet that processes the client sheets from a specified folder.

I am very inexperienced with VBA and macros so I appreciate any help.
Thanks in advance.

I need the ability to copy multiple worksheets from one workbook created via Crystal Reports export (ms excel 2003 format) and merge into one worksheet in ms excel 2010. Currently, I am able to use the openfile dialog to open the 2003 workbook, create a new 2010 workbook, and copy the first 2003 worksheet into sheet 1 of the 2010 workbook.

However, when I copy the second 2003 worksheet, I get the famous "The information cannot be copied because the copy area and the paste area are not the same size and shape.

I am using VBA to perform this activity, but I am not able to get past this error. We can have 2003 workbooks that can have anywhere from 3 to 35 worksheets. The first worksheet will contain the header row and then the maximum number of rows allowed on the worksheet. The other worksheets will contain the maximum number of rows on the worksheet with the last worksheet containing less than the maximum. The number of columns can be varied, but I do not have to worry about copying hiddens rows/columns or copying formulas. It is just a straight copy and paste. However, the sheets in the 2003 workbook must be copied in order from sheet 1, sheet 2, sheet 3, etc as this data will usually be in some sort of sorted order.

        With wbkOldWorkBook
        
            .Activate

            For Each wsOldSheet In wbkOldWorkBook.Worksheets
                wsOldSheet.Activate
                
                wbkNewWorkBook.Activate
                wbkNewWorkBook.Sheets(1).Select
                
                wbkOldWorkBook.ActiveSheet.UsedRange.Offset(0).Clear
                    
                    wsOldSheet.UsedRange.Copy
                    
                    wbkNewWorkBook.Activate
                    wbkNewWorkBook.Sheets(1).Select
                    
                        With Range("A65536").End(xlUp).Offset(1, 0)
                            .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                                False, Transpose:=False
                        '.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
                        '    False, Transpose:=False
                        End With
                    
            Next
        
        End With
So my question is, what am I doing wrong or what am I missing???

Thanks!!!

Hi All,

I need a help in regard to a Macro

I have a spreadsheet which has three tabs.(Everday, Form,Database)

Form tab has a template and Database tab has dept and emial ID's

Everyday I have to send emails to multiple depts mentioned on the Everday tab.

I need a macro in such a way is when I update the dept no's Everday tab and run the macro the form tab data has to be copied in email (screen shot attached in sample workbook) then email should be triggered to the email ID mentioned in the database spreadsheet based on the dept no's mentioned on everday tab.

Hope some one would help me out in this regard so that I would save a lot time for me

Hi

I got some great help from JBeaucaire earlier today with the following code to create an exported sheet with merged data.

Option Explicit

Sub MergeRows()
Dim LR As Long, Rw As Long
Dim delRNG As Range

ActiveSheet.Copy
Range("AM:AM,AP:AP").EntireColumn.Insert xlShiftToRight
Range("AM2") = "Product SKU# additional"
Range("AQ2") = "Quantity additional"
Rows(2).Font.Bold = True
Rows(2).WrapText = True
LR = Range("A" & Rows.Count).End(xlUp).Row
Set delRNG = Range("A1")

For Rw = LR To 6 Step -1
    If Range("AG" & Rw) = Range("AG" & Rw - 1) Then
        Range("AM" & Rw - 1) = Range("AL" & Rw)
        Range("AQ" & Rw - 1) = Range("AP" & Rw)
        Set delRNG = Union(Range("A" & Rw), delRNG)
    End If
Next Rw

delRNG.EntireRow.Delete xlShiftUp

End Sub
Since then I have realized that my customer will also want 2 other exported sheets from the original data.

These sheets are based on the data in col "AL" the data in this col will either be "PAAH" or "PAC"

I would need each set of filtered results to go to a separate sheet.

Is it possible to run the whole thing as a single macro creating a new workbook with 3 sheets titled : Ship List (from the code above) / PAAH / PAC ?

I have been battling with this for about 12 hours and could really do with some help.

Many thanks

Jon

I found the following code that merges one range from all workbooks in a folder into one new worksheet. As of now, the code is copying the range ("B4:L4") on the third tab of every workbook. Is there a way to modify this code to also copy the ranges ("P4:R4") and ("V4:AA4")? Essential I want to be able to copy multiple ranges instead of just one.

Sub MergeAllWorkbooks()
    Dim MyPath As String, FilesInPath As String
    Dim MyFiles() As String
    Dim SourceRcount As Long, FNum As Long
    Dim mybook As Workbook, BaseWks As Worksheet
    Dim sourceRange As Range, destrange As Range
    Dim rnum As Long, CalcMode As Long

    ' Change this to the pathfolder location of your files.
    MyPath = "Z:My DocumentsAnalyst RecommendationsTop AnalystsAnalysts ResultsJP MorganTest"

    ' Add a slash at the end of the path if needed.
    If Right(MyPath, 1) <> "" Then
        MyPath = MyPath & ""
    End If

    ' If there are no Excel files in the folder, exit.
    FilesInPath = Dir(MyPath & "*.xl*")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If

    ' Fill the myFiles array with the list of Excel files
    ' in the search folder.
    FNum = 0
    Do While FilesInPath <> ""
        FNum = FNum + 1
        ReDim Preserve MyFiles(1 To FNum)
        MyFiles(FNum) = FilesInPath
        FilesInPath = Dir()
    Loop

    ' Set various application properties.
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    ' Add a new workbook with one sheet.
    Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    rnum = 1

    ' Loop through all files in the myFiles array.
    If FNum > 0 Then
        For FNum = LBound(MyFiles) To UBound(MyFiles)
            Set mybook = Nothing
            On Error Resume Next
            Set mybook = Workbooks.Open(MyPath & MyFiles(FNum))
            On Error GoTo 0

            If Not mybook Is Nothing Then
                On Error Resume Next

                ' Change this range to fit your own needs.
                With mybook.Worksheets(3)
                    Set sourceRange = .Range("B4:L4")
                End With

                If Err.Number > 0 Then
                    Err.Clear
                    Set sourceRange = Nothing
                Else
                    ' If source range uses all columns then
                    ' skip this file.
                    If sourceRange.Columns.Count >= BaseWks.Columns.Count Then
                        Set sourceRange = Nothing
                    End If
                End If
                On Error GoTo 0

                If Not sourceRange Is Nothing Then

                    SourceRcount = sourceRange.Rows.Count

                    If rnum + SourceRcount >= BaseWks.Rows.Count Then
                        MsgBox "There are not enough rows in the target worksheet."
                        BaseWks.Columns.AutoFit
                        mybook.Close savechanges:=False
                        GoTo ExitTheSub
                    Else

                        ' Copy the file name in column A.
                        With sourceRange
                            BaseWks.Cells(rnum, "A"). _
                                    Resize(.Rows.Count).Value = MyFiles(FNum)
                        End With

                        ' Set the destination range.
                        Set destrange = BaseWks.Range("B" & rnum)

                        ' Copy the values from the source range
                        ' to the destination range.
                        With sourceRange
                            Set destrange = destrange. _
                                            Resize(.Rows.Count, .Columns.Count)
                        End With
                        destrange.Value = sourceRange.Value

                        rnum = rnum + SourceRcount
                    End If
                End If
                mybook.Close savechanges:=False
            End If

        Next FNum
        BaseWks.Columns.AutoFit
    End If

ExitTheSub:
    ' Restore the application properties.
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With
End Sub


I am using Excel 2003 with an excel add-in for SQL Server Analysis Service. The following problem only happens when the add-in is installed. And it happens on multiple machines with the exact same setup.

You can take a blank workbook, merge a couple of cells togther. Lets say cells cells D10:E10:F10 are merged as one cell. You can click on any column and it will highlight just that single column all the way down the workbook, but if you click on column D or E or F, it will highlight D, E, F as one column.

If anyone has any ideas please let me know. Removing the add-in is not an option.

Good afternoon everybody!

The last post I had was titled "Integrating a Shift Schedule and Time Sheets (if I had hair, I would pull it out!)" and I didn't get very far with it. So I have redesigned my time sheets and I now think that the problem that I have might be easier to solve...well that's what I'm hoping anyway!

The attached workbook shows a 4 worksheets. If you move across to the right of the 'Site 1' sheet, you'll see the hours that each employee has worked each day. This then is also shown on the 'Schedule' sheet, this sheet is just so I can collate all schedules together and view/print them easily.

The problem I am having is getting the details to automatically appear on the time sheet on the left of the 'Site 1' and Site 2' sheets. So that each employees names are shown on one line and the accumulated amount of hours for each day show under the corresponding date.

JBeaucaire managed to do it when he was tinkering a previous post 'How to merge multiple lines on a time sheet?!', but I haven't been able to replicate it, due to a lack of understanding.

Any help would be greatly appreciated.
Glenn


No luck finding an answer? You could always try Google.