Loopy Worksheet Problem

[G-fer]

Hi ...

I have a routine that runs continuously and needs to loop through half a dozen worksheets, perform some arithmetic, and then wait for a while before starting again.

I am able to employ Application.ScreenUpdating = False to prevent the display changing while the routine is running, but focus goes to the last sheet altered by the routine when the loop completes.

I would like to return the focus to the sheet that I was viewing when the loop starts, rather than having to reselect it.

How do I assign a variable xxx to the name of the worksheet that was displayed when the loop starts so that I can return to that sheet using something like Worksheets(xxx).Select when the loop finishes?

 

[GTO]

Hi there,

Chances are that you do not need to be selecting the sheets during runtime. That said, maybe:

Sub exa()
Dim wksOrigFocus As Worksheet
    
    If TypeName(ActiveSheet) = "Worksheet" Then
        Set wksOrigFocus = ActiveSheet
    End If
    
    '---your code
    
    If Not wksOrigFocus Is Nothing Then
        wksOrigFocus.Select
    End If
End Sub

Hope that helps,

Mark

 

[G-fer]

Thanks Mark ... I'll give that a shot.

I know that using select isn't very efficient, but I need to do lots of offsets in each sheet, and I can't figure out how to do them without at least selecting the relevant worksheet first.

G-fer

 

[G-fer]

Hi Mark ....

Thanks for the help. All I had to do was make wksOrigFocus public and the rest fell into place.

Regards ...

 

[GTO]

Thanks Mark ... I'll give that a shot.

I know that using select isn't very efficient, but I need to do lots of offsets in each sheet, and I can't figure out how to do them without at least selecting the relevant worksheet first.

G-fer

Are you performing the actions in every worksheet in the workbook?

 

[G-fer]

Sorry ... away from machine for a while ..

No, all but 2

 

[GTO]

Sorry ... away from machine for a while ..

No, all but 2

Okay, by simple example, this would add 2 to the current value of A1 for each sheet, excepting Sheet2 and Sheet3, and doesn't select the sheets.

Sub exa2()
Dim wks As Worksheet
    
    For Each wks In ThisWorkbook.Worksheets
        If Not wks.Name = "Sheet2" _
        And Not wks.Name = "Sheet3" Then
            
            wks.Range("A1").Value = wks.Range("A1").Value + 2
        End If
    Next
End Sub

 

[G-fer]

Thanks Mark ....

This is a big routine here, so it's worth trying to do it more efficiently.

I'm guessing that I would be able to change cells using copy/paste methods instead of selecting and assigning values.

Am I looking in the right direction?

Graham

 

[GTO]

Thanks Mark ....

This is a big routine here, so it's worth trying to do it more efficiently.

I'm guessing that I would be able to change cells using copy/paste methods instead of selecting and assigning values.

Am I looking in the right direction?

Graham

Hi Graham,

Selecting/Activating stuff is the slow part, not assigning the values.

Assigning values like "Sheet2.Range("A1").Value = Sheet3.Range("C3").Value is faster than Copy/PasteSpecial.

Is that of any help?

Mark

 

[G-fer]

Yes thanks Mark ...

I hadn't realised that you could assign data to a cell without selecting it first.

Even though the routine works perfectly well as it is (there's only about half a dozen sheets that need to be accessed), I'm going to rewrite this whole thing just for the sake of learning to do a job well, instead of just getting it done in any "she'll be right" (as we say down here) fashion.

Regards

Graham