Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets

**DHartwig35805** · 08-20-2012, 10:44 AM

ALCON,

I have a macro that will allow me to copy the first worksheet in a list of Excel workbooks, to a new workbook and place all of the worksheets on a single worksheet in the new workbook. That one is fine and I do not wish to break it.

However, I now have a different conundrum to overcome and that is the ability to copy all worksheets, in multiple workbooks, to a new workbook, but this time I want all worksheets copied to separate worksheets in the new workbook. Kind of like when you do it manually, and right click the tab, select 'Move or Copy', then choose which workbook you want them copied to. I want to automate that process down to pushing a button on a worksheet. I know there will be some looping involved, but trying to figure it out on my own without a VBA manual, makes it a little bit harder than usual.

So, has anyone done anything like this before? If so, I would accept any and all useful advice

on this subject.

I'd like to thank everyone in advance for reading my post and for those who answer, my eternal gratitude.

DBH ends......

**JapanDave** · 08-20-2012, 10:53 AM

It certainly can be done. But you will need to explain where the files will be kept? How many if they are not in the same folder location? What sheet or No. of sheets that need to be copied?

**DHartwig35805** · 08-20-2012, 11:01 AM

All the files I will be copying from are in a single folder, and there can be up to 5 sheets per workbook. However, not all workbooks will have 5 worksheets, some will have only 1, some 2, etc. I was going to execute the macro from a file that is one level up from the folder where the data is contained. I was going to prompt (with Inputbox) the user to tell the macro where the files are, and use that entered string to find the files. All files in the folder will be .xlsx files (Excel 2007 format)

**JapanDave** · 08-20-2012, 11:24 AM

So you want all files in that folder and all sheets in each workbook? And you want to prompt the use to select the folder where you want to copy from?

**JapanDave** · 08-20-2012, 11:54 AM

Ok try this. When prompted, select the folder you want to save the files sheet from. It will create a new workbook with the copied sheets.

Sub dave()

    Dim cel As Range
    Dim FileName, ws As Worksheet
    Dim rng As Range
    Dim destWB As Workbook
    Dim pPath As String
    Dim ShellApp As Object
    
    Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, Prompt, 0, OpenAt)
     
    On Error Resume Next
        pPath = ShellApp.self.Path
    On Error GoTo 0
     
    Application.ScreenUpdating = 0
        If pPath = "" Then
        '  Cancel was selected
            MsgBox "Stopping because you did not select a Folder"
            Exit Sub
        End If
          i = 1
        Workbooks.Add
        Set destWB = ActiveWorkbook
           With CreateObject("scripting.filesystemobject")
                    For Each FileName In .getfolder(pPath).Files
                       With Workbooks.Open(FileName)
                         For Each ws In .Sheets
                          ws.Copy destWB.Sheets(i)
                            i = i + 1
                         Next
                         .Close True
                       End With
                   Next
          End With
        Application.ScreenUpdating = 1
End Sub

**DHartwig35805** · 08-20-2012, 12:05 PM

Outstanding, I cannot thank you enough because this is EXACTLY what I was looking for, and you did it a lot easier than I would have done it (newbie VBA programmer that I am).

I wish there was something I could do to help you, and who knows, maybe someday in the future. Thanks again.

**JapanDave** · 08-20-2012, 12:12 PM

Glad I could help.

**sbastin** · 08-23-2012, 12:54 PM

I am trying to take multiple Excel workbooks and merge them into one for summary purposes. What code is best for this? I have tried several and failed. Please help?

**Cutter** · 08-23-2012, 02:38 PM

Hello sbastin, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

**Abhi_1977** · 08-27-2012, 02:45 AM

This is simply great solution. Can anyone guide me how to post my problem

**Cutter** · 08-27-2012, 09:14 AM

Hello Abhi_1977, and welcome to the forum.

Go to the main page and, from the list of sub-forums you see there, choose the one that is most appropriate for your problem. Once in the sub-forum you'll see a blue button labelled "+ Post New Thread" at the top left. Click on that to open a new window where you can type your problem. Make sure to give your thread an acceptable title that complies with rule #1. If you haven't read the rules yet you should click on the Forum Rules button at the top of the page before trying to post.

**Abhi_1977** · 08-27-2012, 11:38 AM

Hello Cutter

Thanks a lot. I have successfully posted my problem.

Regards,

Abhijit.

Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets

Re: Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets

Re: Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets

Re: Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets

Re: Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets

Re: Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets

Re: Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets

Re: Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets

Re: Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets

Re: Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets

Re: Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets

Re: Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions