Display Cell B1 value in Sheet1 of all the workbooks in a "Sample" workbook

Hi All

I have a workbook name Sample and I want the code to run when its opened and it should check the first sheet "Sheet1" in all the workbooks in the folder C:\Folder\Temp.

And if the first sheet name is not "Sheet1" then just ignore that workbook and move to next workbooks. If the first sheet name is "Sheet1" in a workbook then store the value present in cell B1 in Sample workbook "Sheet1".

So if there are 5 workbooks in the folder C:\Folder\Temp and two of them hasn't got first sheet named "Sheet1" then display value of B1 in rest of the 3 sheets to the "Sheet1" of "Sample workbook" one after another.

I hope anyone can help me in this.

Thanks

Yes please. It would be great if the code copies those values in column A.

Please help me in this.

Thanks

Thanks so much Solus for your help. The code works but if there are some other files like word document (.doc)in the folder "C:\Folder\Temp\" , then it gives an error message but I want the code to just ignore those files(e.g word and Power point files) and don't display any error message.

Could you please help me in this?

Thanks

Thats brilliant Solus.Thanks a lot. That problem is resolved and now one more thing is sometimes in the folder few workbooks will be password protected so I even want the code to ignore workbooks which are password protected instead of asking me again and again to enter password of the particular workbook.

Can this be done? Many thanks afor your help so far.

Hi Solus. There are few workbooks which when I open manually then I need to enable macros first and then it asks for password to enter. So when I run this code then it works fine for the workbooks which are not password protected but the ones with password display me a window to enter the password first. SO thats why I just want to ignore the workbooks which are password protected.

It still asks for the password for the protected workbooks.

Any suggestions?
Thanks

Hi izandol

When I run this code then it gives me run time error "Invalid password..." etc.. But in actual I want the code that will just ignore the workbooks with password on and don't ask me any password.

Any suggestions?

Thanks

Izandol why there are 3 End if statements? This might be causing the problem? Could you please check the code again?

Many Thanks

Now I am getting runtime error '-2147221080(800401a8)' Automation error in the following line:

If wbSource.Sheets(1).Name = "Sheet1" Then

Please see the code below.

Please Login or Register  to view this content.

You are genius..Thanks a lot . It worked perfectly fine..