Work on all files in a folder with standard structure

[ShyamExcel]

I have about a 100 files in a folder (.xlsx) which all have the same 5 tabs in them. I need to do these things on all of them.


1. In Tab named 'Dates', there is a drop down in C6 which has to be set to 12/7/2012.

2. Hide Tab named 'Raw'.

3. Delete Tab named 'Old'


Is there Loop routine in VBA that we can write for searching through and performing these 3 actions and saving them in the same files?


Thanks

Shyam

 

[kaushik03]

Hello Shyam,


Please download the files from the below locations


http://speedy.sh/XHRDs/TestB.xlsx


http://speedy.sh/uaXhZ/MyFile.xlsm


Instructions:


1)First link contains three files (TestA, TestB, TestC). Download these files and save them in a folder at your desktop; and name the folder as TestFolder


2) Second link contains the file (Myfile) that has the macro.Download and save the file in your deasktop (or wherever you want),open the file, press ALT+F11 from keyboard to bring the code module and make the change in the below line of code as follows:


It has to be changed from:


strPath = "C:Documents and Settingskaushik DuttaDesktopTestFolder"


To:


strPath = "C:Documents and Settingsyour nameDesktopTestFolder"


[Assuming. you are using this in your desktop ]


Rest of the code will remain the same.


Now, press F5 to run the code...macro will do the job you specified. I have mentioned comments in the code for task1, 2 and 3 as you have specified in your post. This code is applicable to as many as sheets you might have.


Clarification required:


In your first task, where you want C6 (containing drop down) of "Dates" sheet has to be set to 12/7/2012. I do not understand this part properly. Is it a cell drop down done through data validation or you have used some control(I mean, any form control "combobox" etc) which you want to be reset back to "12/7/2012"?....plz calrify.


As of now, code will incorporate this value (12/7/2012) in C6 of "Dates" sheet of all the workbooks.


Kaushik

 

[ShyamExcel]

Thanks Kaushik. I'll try it out.


I should have been clearer. Apologies.


Yes, C6 has a drop down control through validation, which through various vlookup and other formula changes other values in the sheet (based on the selection). It is a dashboard which I want to set it to 12/7/2012 when the sheet is opened.

 

[kaushik03]

Hi Shyam,


I , probably, could share more realistic solution for this part (drop down cell issue) if you could share your sample dashboard with me, because you have said there are lot many formulas which are linked to this cell (C6).


But , meanwhile, you can do something like this:


Private Sub Workbook_Open()


Worksheets("you dashboard sheet name").Activate

Range("C5").Value = "12/7/2012"


End Sub


Double click on ThisWorkbook (left to the code window) and paste the above code.


Whenever you will open your workbook, this code will fire and C6 will always reflect the value as specified.


Let me know, in case you face any issue in executing the solution mentioned in my earlier post.


Kaushik

 

[ShyamExcel]

Thanks a lot for your help Kaushik. Unfortunately I cannot share the workbook. I'll see if can create a dummy workbook if your suggestions hit a roadblock. First let me try them out.


Thanks for your time!


Shyam