merge all worksheets from all XL files in selected folder

M

Mr.Karr

New member
Joined
Oct 21, 2014
Messages
13
Reaction score
0
Points
0
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!
 

Attachments

  • BBC.xlsm
    23.2 KB · Views: 16
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.
 
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
Click to expand...

Please advise. Thanks!
 
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: sorry, it's throwing up error here:
sh.Copy after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
sh.Cells.Copy
Click to expand...


Did you get a chance to see the actual file? Please advise
 
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.
 
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!
 

Attachments

  • sampleFile.xlsx
    484.5 KB · Views: 6
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
 
You must log in or register to reply here.
Back
Top