Results 1 to 8 of 8

Thread: merge all worksheets from all XL files in selected folder

  1. #1

    merge all worksheets from all XL files in selected folder



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    Please see the attached file. The only automation which is pending: while merging the worksheets, copy as values & formats only.

    The automation which I have currently copies worksheets full of zeros as unfortunately those worksheets is full of cube formulas and formats. Can anyone please help provide a code snippet to merge all worksheets to a masterworkbook with values and formats only?

    I have also attached a sample file below.

    Many thanks!
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    This is untested, and not the most efficient, but I think if you just add this:

    Code:
                    sh.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
                    sh.Cells.Copy
                    ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Cells(1, 1).PasteSpecial Paste:=xlValues
    Immediately after the line that reads
    Code:
    sh.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    It should do it. Be warned that it could be slow, and will overwrite every single formula on the worksheet with a value.

    Not my preferred way, but don't have time to look at it more closely at the moment.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hello Ken,

    is the below order correct?

    If Not mybook Is Nothing Then For Each sh In mybook.Worksheets
    sh.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    sh.Cells.Copy
    ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Cells(1, 1).PasteSpecial Paste:=xlValues

    Next sh
    Please advise. Thanks!

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Yes, should be. Basically we're leaving the part where you copied the sheet, as that will get all the formatting. Then we copy the cells of the original sheet and paste them as values over the one you copied into the new file.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    @Ken: sorry, it's throwing up error here:
    sh.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    sh.Cells.Copy


    Did you get a chance to see the actual file? Please advise

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Which line exactly, and what error?

    I got your workbook, but I can't run it as I don't have the files that it's trying to consolidate.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Hi Ken
    Here is the error info:
    Run-time error '1004'
    Method 'Copy' of object'_Worksheet' failed

    sh.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

    Also, I've attached a sample file so you can try. Thanks!
    Attached Files Attached Files

  8. #8
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    370
    Articles
    0
    Excel Version
    2020
    I'd use:

    Code:
    Sub M_snb()
        c00 = "G:\testfolder\"
        c01 = "*.xlsx"
        sn = Filter(Split(CreateObject("wscript.shell").exec("Cmd /c Dir """ & c00 & c01 & """ /b/a").stdout.readall, vbCrLf), ".")
        
        Sheets.Add , , UBound(sn) + 1
        For j = 0 To UBound(sn)
            With GetObject(c00 & sn(j)).Sheets(1).UsedRange
                Sheets(Sheets.Count - j - 1).Cells(1).Resize(.Rows.Count, .Columns.Count) = .Value
                .Parent.Parent.Close 0
            End With
        Next
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •