Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

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
0
bvanscoy678
Asked:
bvanscoy678
  • 11
  • 10
1 Solution
 
dlmilleCommented:
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
0
 
dlmilleCommented:
Additional question - do you want to select the files that get imported, or are they all contained in one directory?

Dave
0
 
dlmilleCommented:
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
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
dlmilleCommented:
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
0
 
bvanscoy678Author Commented:
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
0
 
dlmilleCommented:
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
0
 
bvanscoy678Author Commented:
I am looking at examples now.
0
 
bvanscoy678Author Commented:
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.
0
 
bvanscoy678Author Commented:
Okay, I got it to work via the macro, not the button. I am going to make a few copies and test.
0
 
bvanscoy678Author Commented:
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
0
 
bvanscoy678Author Commented:
actually there are 2 modules that contains several macro's.

0
 
dlmilleCommented:
Here's a 2003 version of the same

Dave
ImportAppendWorkbooks-v1.xls
0
 
dlmilleCommented:

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
0
 
bvanscoy678Author Commented:
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
0
 
dlmilleCommented:
Done - changes requested completed and now - this should work in Excel 2003 as well.

Dave
ImportAppendWorkbooks-v2.xls
0
 
bvanscoy678Author Commented:
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.
0
 
dlmilleCommented:
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
0
 
bvanscoy678Author Commented:
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
0
 
dlmilleCommented:
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
0
 
DaveCommented:
While this is closed you may want to look at my article here, http://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
0
 
bvanscoy678Author Commented:
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
0
 
dlmilleCommented:
No problem and my pleasure.  Please don't hesitate to ask follow-up questions.

and have a better day tomorrow :)

Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now