Please help...I have the code !

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
guys...

cant figure out what exactly this code is doing? My problem is - I have different sheets in different workbooks. I want to transfer each and every sheet from these different workbooks into one single workbook. I have the code which I know (mentioned below) does meet my requirement to a certain extent but am unable to figure out wat exactly happens. If it does - then where do I put the code? Is it that I need to paste it as a module in that single workbook where I want all the sheets to be gathered?

Code:

Sub allWorkbook()
'Standard Module code, like: Module1!
Dim f%, Wb

Application.ScreenUpdating = False

With Application.FileSearch
.NewSearch

'Option: Search Sub-Folders as well?
.SearchSubFolders = False 'Option: True or False!

'Option Current Folder or a defined folder?
'.LookIn = CurDir
'Or
.LookIn = "C:\your directory"

'Option: Only Search this type of file?
.Filename = "*.xls"

.Execute

For f = 1 To .FoundFiles.Count
Set Wb = Workbooks.Open(Filename:=.FoundFiles(f))

'*************************************************************************
'Add your code here!
'*************************************************************************

ActiveWorkbook.Save
ActiveWorkbook.Close

Next f
End With

Application.ScreenUpdating = True
End Sub

I do understand that the code is asking for a folder where it can search that xls files but am in a little mess ! Please help !

thanks so much for your help !
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
ok !!!! Now I understand...we have an ego problem out here ! nice going fellas...well this message board aint the only last place on earth where I can find help...! thanks but no thanks for your help !
 
Upvote 0
1. go to the VBA Editor
2. double click somewhere in the project browser (top-left explorer-like window), like on thisworkbook
3. click on code editor on the right
4. paste the code
5. press F5 to execute

The code would fit best inside a module though. For more information about that use the search function or find a VBA tutorial :LOL:

Cheers!
 
Upvote 0
well...thanks Harvey...for the walkthru...I know that too but it is giving me a compile time error when I execute the code...wud appreciate if u cud help me more on this !
 
Upvote 0
when I execute it, it works the way it should (I guess its function is to open all workbooks in a given directory)
I tried it in a private module and in the thisworkbook module.
What is the error message?
 
Upvote 0
Msgbox--> Compile error:Expected Function or variable

It highlights the word "workbooks" in the below mentioned line:
Set Wb = workbooks.Open(Filename:=.FoundFiles(f))

I have set my directory (exact path on primary drive of my PC) by enabling the following line in the code:

.LookIn = "C:\Documents and Settings\nimit\Desktop\take\"

Am I going wrong somewhere? or do I need to do modifications to the existing code...wat do u suggest sir?
 
Upvote 0
You can try removing some brackets here:
Code:
Set Wb = workbooks.Open(Filename:=.FoundFiles(f))
so it ends up like this:
Code:
Set Wb = workbooks.Open Filename:=.FoundFiles(f)
but honestly, I have no real idea of why that error should happen. Do you have other code in the module? Maybe a typo somewhere?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top