Copying and pasting worksheets into new worksheet as values

**MikeFranz123** · 05-03-2012, 05:10 AM

Hi everyone,

I'm trying to copy several sheets from one workbook to another, which I've managed to do, but unfortunately it is paste formulas, not values.
Here is the code I'm using:

    For i = 1 To 4
    Windows(MyArray(i)).Activate ' this is an array that contains the files from which I want to copy the sheets
    Sheets(Array("Overview", "Expected orders", "Numbers Per Month")).Copy before:= _
        Workbooks("Overall Status Report.xlsm").Sheets(1) ' this copies and pastes the sheets from the workbook in the "MyArray" array into the desired     'workbook. I think here somewhere is where I would need to specify that I want it to paste values.
    Workbooks(MyArray(i)).Close False
  Next i

Any help would be appreciated!

Thanks

Mike

**arlu1201** · 05-03-2012, 05:22 AM

In the above code, you are making a copy of the sheets, hence its directly copying the data as is.

You might need to change the code to copy the contents individually into the destination file. By doing that, you will be able to paste special values.