Optimize this?

Jokada

New Member
Joined
Dec 6, 2005
Messages
12
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:

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:

Code:
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!!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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.
Code:
'-------------------------------------------------------
'- 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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,811
Messages
5,574,448
Members
412,595
Latest member
slim313
Top