Modify this awesome code snippet

[SirKT]

Hello

Can someone please help modify the attached automation. Click open the attached file and you'll see a macro button which is capable of doing the following;

1. Opens up file explorer
2. Browse & select a folder
3. Move/copy worksheets to the masterworkbook

But the problem is, this automation doesn't paste as values but all the values are transferred to the master workbook as zeros, as the imported worksheets were full of cube formulae and conditional formatting.

Please help modify the attached automation. Many thanks!

 

[Smallman]

Hi

Replace this line of code

            For Each sh In mybook.Worksheets
                sh.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            Next sh

With something like this.

Dim ws as worksheet      
            For Each sh In mybook.Worksheets
                Set ws = ThisWorkbook.Sheets.Add
                sh.[a1].CurrentRegion.Copy
                ws.[a1].PasteSpecial xlPasteValues
            Next sh

Now I don't know the structure of the files you are bringing into your workbook I just assumed they were set out sensibliy with the header row on top starting in A1 and your data being tabular. A1.currentregion will work if you have set up your data tabs logically. Change to suit

Take care

Smallman

 

[SirKT]

Hi

Data worksheets which neither follow a procedure like tables nor currentregion selection would help.
Requirement is simply to move/copy worksheets to the masterworkbook along with the values, unchanged.

We will wait & see if the available Ninjas and other experts can crack this up.

 

[Smallman]

Sirkt

That code works wonderfully on my machine. You did not provide a source file which means no one can know what the structure of the data you are importing is. The source file is just as important as the file you provided. I have uploaded dozens of files in this way and be assured the above will work there just needs to be some consistency and logic in the way your data is set out. i am sure there is.

You will need to upload this source file in order for others to assist you in my opinion.

Smallman

 

[SirKT]

Hi

Sure I will upload a sample source file shortly

 

[SirKT]

@Smallman
sample file here. Also I have attached an updated masterworkbook. Please try.

If that doesn't work, we will figure out a way to covert the formulaValues to values only

 

[Smallman]

Hi Sirkt

Yours is not a straight forward solution because of the OLAP formula in your file. I ran a test on some new coding put some dummy files in a temp directory and the code worked a treat. Here is the generic code:

Option Explicit
 
Sub OpenImp1()
    Const sPath = "D:\Temp\" 'Change to suit
    Dim sFil As String
    Dim owb As Workbook
    Dim ws As Worksheet
    Dim sh As Worksheet
   
    Set ws = Sheet1
    sFil = Dir(sPath & "*.xl*")
 
    Do While sFil <> ""
        Set owb = Workbooks.Open(sPath & sFil)
        For Each sh In ActiveWorkbook.Sheets
            sh.[A2:BZ500] = sh.[A2:BZ500].Value
            sh.Copy After:=ws
            owb.Close False 'Close no save
        Next sh
        sFil = Dir
    Loop
End Sub

So that brings me to your problem and why it will not either overwrite the values of your workbook or copy and paste the values (I tried both methods and glaring failure met me). So I don't understand. It seems like we are not even dealing in an Excel spreadsheet.

I will have another crack later in the day. If you find something in the mean time by all means share it.

Take care

Smallman

 

[SirKT]

@Smallman : sorry it's throwing up an error here:

Run-time error '1004'
Method 'Copy' of object'_Worksheet' failed

Is there any other solution available ?

 

[SirKT]

ok, found something what would fix this;
above code actually copies worksheets (ws.copy) as this woudn't sure to paste as values. will they?

1. Copy the range
2. Switch windows
3. Add a new worksheet ( to the worksheets referenced as mybook)
4. Then paste the range as values

Done!

 

[Smallman]

@SirKT

I almost always test my code before I post it in an open forum. So that error message you got I don't get in a pure test environment. When you say the above code actually copies worksheets that is what your original code was doing. So I just replicated this. If you wanted something else just say what deviations you want.

The code actually opens a workbook pastes and changes all the formulas to values. It then imports the sheet and closes the workbook without saving so the original formulas are protected. I have written an article on the subject here.

SmallmanCopySheets

I am glad you solved your problem. Be nice to see the final coding.

Take care

Smallman