Array VBA issue

[WindsorKnot]

Hi,

I'm trying to make my code run faster by using a dynamic Array.

However, I keep on getting an error everytime I run my code. The error is Object variableor with block variable not set.

Where the error occurs is highlighted in red.

Can someone help me out with this particular issue?

Thanks.

Sub rangevalue()

    Application.ScreenUpdating = False
    Dim lsheets() As Worksheet, i As Integer, NSheets as Integer
 
    Nsheets = ActiveWorkbook.Worksheets.Count
    ReDim lsheets(1 To Nsheets) ' Declares the array variable size

    For i = 1 To Nsheets
        [COLOR=red][B]lsheets(i).Activate[/B][/COLOR]
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues
    Next i
    Application.CutCopyMode = False
End Sub

 

[RoryA]

You haven't actually assigned anything to the array, and to be honest I don't see what purpose it serves here. Try:

Sub rangevalue()

    Application.ScreenUpdating = False
    Dim i As Integer, NSheets as Integer
 
    Nsheets = ActiveWorkbook.Worksheets.Count
    For i = 1 To Nsheets
        with sheets(i).usedrange
            .copy
            .PasteSpecial Paste:=xlPasteValues
        end with
    Next i
    Application.CutCopyMode = False
End Sub

 

[excel-proficient]

You can chage

lsheets(i).Activate

to

Worksheets(i).Activate

Thanks

 

[TheNoocH]

try something like this...

Sub rangevalue()
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        ws.Cells.Copy
        ws.Cells.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    Next ws
End Sub

 

[WindsorKnot]

You haven't actually assigned anything to the array, and to be honest I don't see what purpose it serves here. Try:

Sub rangevalue()
 
    Application.ScreenUpdating = False
    Dim i As Integer, NSheets as Integer
 
    Nsheets = ActiveWorkbook.Worksheets.Count
    For i = 1 To Nsheets
        with sheets(i).usedrange
            .copy
            .PasteSpecial Paste:=xlPasteValues
        end with
    Next i
    Application.CutCopyMode = False
End Sub

Hi Rory,

The purpose is the value range the entire workbook, so it kills all formulas, leaving only the values.There is some additional code that I pulled out to make my post simplier.

I basically wanted to use an array since I want to incorporate arrays in my code more.

Thanks.

 

[WindsorKnot]

try something like this...

Sub rangevalue()
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        ws.Cells.Copy
        ws.Cells.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    Next ws
End Sub

Thanks. This is actucally what my code looks like now. I wanted to try and do this using an array instead. Not sure if this is possible.

 

[RoryA]

That's what the code I posted does. There's no point using an array here as you would need to loop to assign each sheet to the array, then loop again to manipulate each sheet.

 

[WindsorKnot]

Ok fair enough. It definitely speeded up my code noticably compared to what I had prior to my array "experiment"