xluser2007
04-19-2009, 07:17 AM
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.
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.