Optimize this?

[Jokada]

Hi,

I'd like to remove all the linked values in a file and replace them by the actual values.

I've created this code:

 For j = 1 To tWBo.Worksheets.Count
                    Sheets(j).Select
                    Cells.Select
                    Selection.Copy
                    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                Next j

More efficient would be if I can make a dynamic array with the number of sheets.
I.e:

Sheets(Array(1,2,3,4,5)).copy

Is there a way to create an array with size (Worksheets.Count - 1)
And then fill with 1, 2, 3, ... till (Worksheets.Count - 1)?

Any help would be greatly appreciated!!

 

[BrianB]

I think your basic method is OK, however I have not had much use for Copy/PasteSpecial like this - so could be wrong.

You might like to test these 2 versions and let us know which is best.
Version 2 assumes that the ActiveSheet.UsedRange is the same or larger than in the other sheets. You could make it ActiveSheet.Cells.Copy but this would add to the processing time.

'-------------------------------------------------------
'- loop through sheets
Sub test1()
    Dim ws As Worksheet
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        ws.UsedRange.Copy
        ws.UsedRange.PasteSpecial Paste:=xlValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    Next
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = True
    MsgBox ("Done")
End Sub
'------------------------------------------------------------------
'- select all sheets
Sub test2()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Worksheets.Select
    ActiveSheet.UsedRange.Copy
    ActiveSheet.UsedRange.PasteSpecial Paste:=xlValues, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = True
    MsgBox ("Done")
End Sub