Clearing Ranges from Multiple workbooks.
 

Hi,

I'm trying to create some code that will open (30) workbooks and clear
multiple named ranges from each workbook. The code opens the workbooks fine
but I get a Global error when code reaches the clearcontents line. The code I
have is listed below, the on error is put in because a few of the workbooks
do not have all three named ranges. All of them have the first two named
ranges but only some of then have the third named range.

Thanks for any help you can provide.

Dim a As Integer
Dim wkbk As Workbook

a = MsgBox("Are you sure you want to clear previous data?", vbQuestion +
vbYesNo, "Budget")
Select Case a
Case vbYes
Run ("OpenWorkbooks")
'On Error Resume Next
For Each wkbk In Workbooks
wkbk.Activate
Range("CAPITALEXP").ClearContents
Range("EXPENSES").ClearContents
Range("INCOME").ClearContents
Next wkbk
Run ("CloseWorkbooks")
Case vbNo
End Select
End Sub

Clearing Ranges from Multiple workbooks.
 

Try

For Each wkbk In Workbooks
wkbk.Activate
wkbk.Names("CAPITALEXP").ReferstoRange.ClearConten ts
wkbk.Names("EXPENSES").ReferstoRange.ClearContents
wkbk.Names("INCOME").ReferstoRange.ClearContents
Next wkbk

--
Regards,
Tom Ogilvy

"Steve O" wrote in message
...
Hi,

I'm trying to create some code that will open (30) workbooks and clear
multiple named ranges from each workbook. The code opens the workbooks
fine
but I get a Global error when code reaches the clearcontents line. The
code I
have is listed below, the on error is put in because a few of the
workbooks
do not have all three named ranges. All of them have the first two named
ranges but only some of then have the third named range.

Thanks for any help you can provide.

Dim a As Integer
Dim wkbk As Workbook

a = MsgBox("Are you sure you want to clear previous data?", vbQuestion +
vbYesNo, "Budget")
Select Case a
Case vbYes
Run ("OpenWorkbooks")
'On Error Resume Next
For Each wkbk In Workbooks
wkbk.Activate
Range("CAPITALEXP").ClearContents
Range("EXPENSES").ClearContents
Range("INCOME").ClearContents
Next wkbk
Run ("CloseWorkbooks")
Case vbNo
End Select
End Sub

Clearing Ranges from Multiple workbooks.
 

You could use the following

For Each wkbk In Workbooks
wkbk.Activate
wkbk.Sheets(1).Range("CAPITALEXP").ClearContents
wkbk.Sheets(1).Range("EXPENSES").ClearContents
wkbk.Sheets(1).Range("INCOME").ClearContents
Next wkbk

Alok

"Steve O" wrote:

Hi,

I'm trying to create some code that will open (30) workbooks and clear
multiple named ranges from each workbook. The code opens the workbooks fine
but I get a Global error when code reaches the clearcontents line. The code I
have is listed below, the on error is put in because a few of the workbooks
do not have all three named ranges. All of them have the first two named
ranges but only some of then have the third named range.

Thanks for any help you can provide.

Dim a As Integer
Dim wkbk As Workbook

a = MsgBox("Are you sure you want to clear previous data?", vbQuestion +
vbYesNo, "Budget")
Select Case a
Case vbYes
Run ("OpenWorkbooks")
'On Error Resume Next
For Each wkbk In Workbooks
wkbk.Activate
Range("CAPITALEXP").ClearContents
Range("EXPENSES").ClearContents
Range("INCOME").ClearContents
Next wkbk
Run ("CloseWorkbooks")
Case vbNo
End Select
End Sub

Clearing Ranges from Multiple workbooks.
 

Thanks, Alok that cleared all the ranges.

I appreciate your help.

"Alok" wrote:

You could use the following

For Each wkbk In Workbooks
wkbk.Activate
wkbk.Sheets(1).Range("CAPITALEXP").ClearContents
wkbk.Sheets(1).Range("EXPENSES").ClearContents
wkbk.Sheets(1).Range("INCOME").ClearContents
Next wkbk

Alok

"Steve O" wrote:

Hi,

I'm trying to create some code that will open (30) workbooks and clear
multiple named ranges from each workbook. The code opens the workbooks fine
but I get a Global error when code reaches the clearcontents line. The code I
have is listed below, the on error is put in because a few of the workbooks
do not have all three named ranges. All of them have the first two named
ranges but only some of then have the third named range.

Thanks for any help you can provide.

Dim a As Integer
Dim wkbk As Workbook

a = MsgBox("Are you sure you want to clear previous data?", vbQuestion +
vbYesNo, "Budget")
Select Case a
Case vbYes
Run ("OpenWorkbooks")
'On Error Resume Next
For Each wkbk In Workbooks
wkbk.Activate
Range("CAPITALEXP").ClearContents
Range("EXPENSES").ClearContents
Range("INCOME").ClearContents
Next wkbk
Run ("CloseWorkbooks")
Case vbNo
End Select
End Sub