Solved: WorkSheet range Activate issue

[xluser2007]

Hi All,

I have the following code:


Sub Clear_BrownTabs(rngWorkbooksListtoOpen As Range)

Dim rngoneCell As Range
Dim wbTarget As Workbook
Dim wksht As Worksheet

'----------------------------------------------------------------------------
' Set Calcualtion mode to manual and screenupdating to false for EFFICIENCY
'----------------------------------------------------------------------------

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

' If the Range specified is incorrect, then give user an appropriate message

If rngWorkbooksListtoOpen Is Nothing Then

Call MsgBox("The Range specified doesn't exist:" _
& vbCrLf & "" _
& vbCrLf & "Please review the range reference specified, update the code appropriately and re-run." _
, vbCritical Or vbDefaultButton1, "The Range specified doesn't exist!")


End If

For Each rngoneCell In rngWorkbooksListtoOpen

If FileFolderExists(CStr(rngoneCell.Value)) = True And IsFileOpen(CStr(rngoneCell.Value)) = False Then

Set wbTarget = Workbooks.Open(FileName:=CStr(rngoneCell.Value), UpdateLinks:=0)

' First loop through and remove all autofilters in the target workbook

For Each wksht In wbTarget.Worksheets

wksht.AutoFilterMode = False

Next wksht

' Now loop through and clear contents in brown tabs depending on the
' type of Brown tab e.g. IBNR

For Each wksht In wbTarget.Worksheets

Select Case UCase(wksht.Name)

Case "IBNR"

wksht.Range("A:N").ClearContents
wksht.Range("A1").Activate

Case "SCALING"

wksht.Range("5:65536").ClearContents
wksht.Range("A1").Activate

Case "VALUATION FACTORS"

wksht.Range("B:F,T:X,AL:AP,BD:BH,BV:BZ,CN:CR,DF:DJ").ClearContents
wksht.Range("A1").Activate

Case "ASSUMPTIONS"

wksht.Range("D:T").ClearContents
wksht.Range("A1").Activate

Case "ASSUMPTIONS_BLENDED"

wksht.Range("D:T").ClearContents
wksht.Range("A1").Activate

Case "PMT RATES"

wksht.Range("A:Z").ClearContents
wksht.Range("A1").Activate

Case "PMT RATES_BLENDED"

wksht.Range("A:Z").ClearContents
wksht.Range("A1").Activate

Case Else

wksht.Range("A1").Activate

End Select

Next wksht

End If

Next rngoneCell

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub


Sub test5()

OPEN_Workbooks_and_ClearBrownTabs ThisWorkbook.Worksheets("CLEAR Brown Tabs by TYPE").Range("ClearBROWNTabs_Tools_FOMLIAB")

End Sub



It basically goes through and clears contents from certain specified tabs depending on their names.

If the tab name doesn't meet the specified tab names, then I would like to Select/ Activate the range("A1") in the worksheet.

I;ve tried debugging and after stepping through the code throws and error:

Error 1004 ("Activate method of range class failed")

in the line:

wksht.Range("A1").Activate

Could anyone please help correct for this error.

Any help appreciated.

[xluser2007]

Ok I tried replacing

wksht.Range("A1").Activate

it with:

wksht.Activate
Range("A1").Activate

and it worked

But I would like to know whether I should use activate or select?

[DannyUk]

Don't think it matters which one

You could also try

Application.Goto Reference:=wksht.Range("A1")

Danny

[Bob Phillips]

A workbook with ALL of the code would help more.

[xluser2007]

A workbook with ALL of the code would help more.

Sorry Bob, I thought I may have pasted a little too much initially :)!

I thought that because I had isolated the error to just the single line of code, ALL the code may have been irrelevant, but I'll paste ALL of it next time.

Thanks

[Bob Phillips]

You cann never have too much :). It is one of the advantages of VBAx that we can post workbooks.

Did Danny's suggestion solve it for you?

[xluser2007]

You cann never have too much :). It is one of the advantages of VBAx that we can post workbooks.


Very true, one of the reasons I enjoy posting and reading the deeper VBA queries that people post here.


Did Danny's suggestion solve it for you?

I had actually gone offline, by the time Danny's suggestion had come through.

I actually went ahead with:

wksht.Activate
Range("A1").Select

As it is quite intuitive for anyone else picking up the code.

Though Danny's suggestion works well indeed, another one in the ToolKit. Thanks Danny!