Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing Multiple Filters? | Excel Discussion (Misc queries) | |||
copy dynamic ranges in multiple workbooks based on a data in 1 col | Excel Programming | |||
How to Link named ranges from multiple Workbooks into a single Wo. | Excel Discussion (Misc queries) | |||
Clearing Data ranges from Multiple Worksheets | Excel Programming | |||
Clearing a Column range in Multiple Workbooks | Excel Programming |