VBA to clear cell across all sheets in a workbook.

[BradyBunches]

I think this is pretty easy, but I'm a total newb and would like to learn how to do this:

Part A
I have a collection of workbooks with about 50 sheets in each workbook. I need to clear the value from cell B15 in every sheet in every workbook.

Part B
In the above workbooks, B15 is a data validation drop down list. I use a hidden worksheet called Drop_DownList to generate the list. B15 uses the values in Column A of the hidden worksheet to generate the dropdown list. I also need to clear the values in column A of the hidden worksheet called Drop_DownList.

Part C
Finally, I'd love to figure out a way to do this as a batch so we could dump the workbooks into a folder as needed and run the script against all the workbooks in that folder.

I know A and B are possible, but I'm not sure if I can automate the whole thing.

Any ideas?

Thanks,
Brady

[snb]

Yes you can. And if you want help you should post an example workbook.

[antoka05]

You could use VbScript for batch run.
Put this code in text file and save it with vbs extension:

dim fso, myPath
'--------------------------------------------------------------------
myPath = "C:\...\"
'--------------------------------------------------------------------
Set fso = CreateObject("Scripting.FileSystemObject")
Set xl = CreateObject("Excel.Application")
Set objFolder = fso.GetFolder(myPath).Files
For Each myFile In objFolder
   set myWb = xl.Workbooks.open (myFile)
   for each mySh in myWb.sheets
      if lcase(mySh.name) = "drop_downlist" then
         mySh.range("a:a").ClearContents
      else
         mySh.range("b15").ClearContents
      end if
   Next
   myWb.Close True
Next
xl.quit
Set fso = nothing
Set xl = nothing
Set objFolder = nothing

Regards,
Antonio

[snb]

To clear cell B15 in every sheet: and the hidden dropdowndata:

sub snb()
  Sheets(1).[B15].Clear
  Sheets.FillAcrossSheets Sheets(1).[B15]
  Sheets("drop_downlist").columns(1).clearcontents
End Sub

[BradyBunches]

Antonio,

I've been modifying your script to suit my purposes and made a couple of changes.

Here's what I have so far:

dim fso, myPath
'--------------------------------------------------------------------
myPath = "C:\Target\"
'--------------------------------------------------------------------
Set fso = CreateObject("Scripting.FileSystemObject")
Set xl = CreateObject("Excel.Application")
Set objFolder = fso.GetFolder(myPath).Files
For Each myFile In objFolder
   set myWb = xl.Workbooks.open (myFile)
   for each mySh in myWb.sheets
      if mySh.name = "Drop-down Lists" then
         mysh.cells.clear
      else
         mySh.range("b15").MergeArea.ClearContents
      end if
   Next
   myWb.Close True
Next
xl.quit
Set fso = nothing
Set xl = nothing
Set objFolder = nothing

The changes I made include:
1. Removed the lcase function to match the sheet name exactly
2. We decided to clear the entire sheet so I substituted cells.clear for the A:A range.
3. I added MergeArea to remove cell B15

Thanks for all your help.

One more thing that I'm trying to do:

Can I set a path to be scanned recursively for workbooks, find them and process them?

I'm having a hard time figuring that out.

Thanks again,
Brady