asked on

Merge multiple Workbooks into one with marco

Hello,

I have searched the previous questions and see there is multiple ways of doing this, so not sure of the simplest way for my project. I completed my project to collect time for each employee. We have 6 supervisors that will collect about 10-15 workbooks for their department. I want to find the easiest way for them to take the 10-15 workbooks and combine into 1 workbook that they can forward to payroll. The supervisors are not very savy in excel, so my thought was to use a macro to help them accomplish this.

Each workbook is identical in structure. Just different names and times they worked.
As far as what gets copied over, I can copy the entire worksheet or just a range. I would like to keep the page format and values, but dont' care about formulas if I don't need them. Example for attached worksheet range would be A1:L75.

I tried recording a macaro and use move/copy sheet, but it won't work because each worksheet has a different name.

expert-idea-for-merge.xls

dlmille

Let's focus on the problem statement:

1. You'll have 10-15 workbooks in a directory
2. You want to combine those together into one driver workbook. How do you want to import? All sheets, or specific sheets?
3. You want to be able to specify the entire worksheet or just a range - with just a range is that to be in one worksheet or separate worksheets.

You're pretty broad in your need and if you can provide some clarification, the scope of your requestion would be better understood.

I've just finished 2 similar requests that went on for a while because these types of questions weren't answered up front.

So think about it and provide additional clarificaiton as needed, with a focus on my 3 points, above.

Dave

dlmille

Additional question - do you want to select the files that get imported, or are they all contained in one directory?

Dave

dlmille

Here's a starting point from a prior question. This one prompts the user for specific columns to import, and if nothing is selected then it brings the entire sheet in for every sheet found in the files selected.

Let me know what changes from here you'd like as you consider the questions I've posed.

Dave
ImportAppendWorkbooks-v1.xlsm

dlmille

It would be easy enough to prompt for a range to import - but one sheet, all sheets?

Finally, this second option merges one file on top of another, all sheets.

So the possibilities are endless, please respond with your clarified scope and we'll wrap this up.

Cheers,

dave
ImportAppendWorkbooks-v2.xlsm

Brent

ASKER

Hi Dave,

There is just one worksheet per workbook (I am going to delete the instruction worksheet that is in the attached example). So, just the one worksheet.
It can be the entire worksheet, I don't have to worry about the range, but if it makes the file smaller, the range would be a1:az100

I would like to bring over the entire worksheet as it is in the invidivual books., formatting et al.

I would guess that picking the files would be the best choice. Most of the people will save the workbooks into a folder per pay period.

I hope this detail helps.
thanks

dlmille

My first post - ImportAppendWorkbooks-V1 is closest to that. Please give it a try and see if you like the output. It prompts for an import column range, but you can just not fill that out - hit OK and see what happens.

It will open each file selected, and bring the sheets over with a pasteall

Dave

Brent

ASKER

I am looking at examples now.

Brent

ASKER

I am using excel version 2003 at work. I got a warning that active X controls will not be opened.

when I click on the macro, nothing happens.

Brent

ASKER

Okay, I got it to work via the macro, not the button. I am going to make a few copies and test.

Brent

ASKER

Okay, I have a command button, but no macro assigned.
I see 3 separate macro's.

open multiple file
open single file
show_browse_directory_dialog.

Not sure how to proceed

Brent

ASKER

actually there are 2 modules that contains several macro's.

dlmille

Here's a 2003 version of the same

Dave
ImportAppendWorkbooks-v1.xls

dlmille

I got rid of the active-x control and added the forms control and this code behind it, in the main module

Sub Button3_Click()
Call ClearWorksheets
Load UserForm1
UserForm1.Show
End Sub

Brent

ASKER

Okay. I put it on my laptop to see it.

I put 3 worksheets into a folder. Selected them. It made all 3 sheets, plus and extra sheet (named it same as one with _4).

Then I got an "out of range error". It showed a bug with
sheets("control Panel"). Activate

This is what I was looking for. If I could skip the first step of asking what range, that would be perfect.

Not sure how to get it to work in excel 2003, since that is what my work has.

Thanks

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brent

ASKER

Yes, this worked just fine. I took the files, then renamed with with an _2.

Can I edit it so it does not asked to clear all non control panel sheets? I will always want the entire sheet.

Thank you very much for the help.

dlmille

the prompt asks if you want to clear all worksheets that are present at the time you run the macro. Are you going to NEVER delete what you've imported?

The prompt has nothing to do with whether you want to import entire sheets - that's built in. This is just housecleaning to say, do you want me to start from scratch, or should I just keep adding tabs?

Get it?

Dave

Brent

ASKER

Dave,

I really do appreciate the help. I like learning on EE, even when it is over my head like this project. Obviously I don't know much at all about code or what this does.

So, the information about the prompt and what it does is very helpful. Plus, knowing that it is a house cleaning explains what it is doing.

The little jab about starting from scratch and adding tabs, well not needed.

Not sure what I did to deserve the sarcism, but I do apologize.

Again, Thanks for the help.

Brent

dlmille

My apologies - I wasn't even attempting to be sarcastic - it was a bad choice of words. Not a jab at all.

No need to apologize - its all my bad. Unlink most posts, I just said "try this" with no explanation - so that wasn't very good of me....

Let me rectify a bit...

Ok - the app first prompts for housecleaning, then prompts for files to select - if you hold ctrl key, you can select multiple files for import.

It opens each workbook for copy - turning off any links - then copies all sheets into the driver workbook (where the macro is running), naming those sheets after the file where they were imported. then closes that workbook.

That's it.

Here's the primary code:

Sub ClearWorksheets()
Dim xMsg As Integer
Dim myWks As Worksheet

Application.DisplayAlerts = False

    xMsg = MsgBox("Clear all non Control Panel Sheets?", vbYesNo, "Hit No to preserve existing sheets")
    If xMsg = vbYes Then
        For Each myWks In ActiveWorkbook.Sheets
            If myWks.Name <> "Control Panel" Then
                myWks.Delete
            End If
        Next myWks
    End If
    
Application.DisplayAlerts = True
End Sub
Sub ImportAppendWorkbooks(sRng As String)
Dim myWkb As Workbook
Dim myWks As Worksheet
Dim myCell As Range
Dim fname As Variant
Dim fnWkb As Workbook
Dim shtCnt As Integer
Dim xMsg As Integer
Dim myTempWks As Worksheet

Application.DisplayAlerts = False

        Set myWkb = ActiveWorkbook
        fname = OpenMultipleFilesFCN(True)
        Application.EnableEvents = False 'to prevent event handling by full sheet copies importing sheet code over
        If IsArray(fname) Then
            For i = LBound(fname) To UBound(fname)
                Workbooks.Open Filename:=fname(i), UpdateLinks:=2, ReadOnly:=1
                Set fnWkb = ActiveWorkbook
                shtCnt = 1
                For Each myWks In ActiveWorkbook.Sheets
                    If sRng <> "" Then
                        myWkb.Activate
                        Set myTempWks = Worksheets.Add(after:=myWkb.Sheets(myWkb.Sheets.Count))
                        For Each r In Range(sRng).Areas
                            fnWkb.Activate
                            Range(r.Address).Copy
                            myWkb.Activate
                            Range(r.Address).PasteSpecial xlPasteAll
                            Application.CutCopyMode = False
                        Next r
                        myWks.Activate
                    Else
                        myWks.Copy after:=myWkb.Sheets(myWkb.Sheets.Count)
                    End If
                    
                    myWkb.Activate
                    If shtCnt = 1 Then
                        ActiveSheet.Name = Left(Left(fnWkb.Name, InStr(fnWkb.Name, ".x") - 1), 27) 'max sht name 31 chars, leaving rooom for sheet number
                    Else
                        ActiveSheet.Name = Left(Left(fnWkb.Name, InStr(fnWkb.Name, ".x") - 1), 27) & "_" & shtCnt
                    End If
                    shtCnt = shtCnt + 1
                Next myWks
                fnWkb.Close savechanges:=False
            Next i
        End If
        Application.EnableEvents = True
        Sheets("Control Panel").Activate

Application.DisplayAlerts = True
End Sub

Open in new window

Please let me know where I can further assist.

Dave

Dave

While this is closed you may want to look at my article here, https://www.experts-exchange.com/A_2804.html which provides a method (and explanation) to either

i. Collate all sheets from all Excel workbooks in a single folder into a single summary worksheet
ii. Collate all sheets from all Excel workbooks in a single folder into a single summary workbook
iii. Collate all sheets from a single Excel workbook into a single summary worksheet

Cheers

Dave

Brent

ASKER

Okay, I will look back at the code. Even though I am not very familiar, every little bit helps. If I use someone's code I at least like to
try and figure it out, so if something goes wrong, I can pick through it.

No biggie, I just wanted to make sure I did not do anything wrong. Tough day at work, probably just took it wrong.

Sorry. No worries.

thanks for the help.

Brent

dlmille

No problem and my pleasure. Please don't hesitate to ask follow-up questions.

and have a better day tomorrow :)

Dave