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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
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 !
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953

ADVERTISEMENT

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!
 

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
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 !
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953

ADVERTISEMENT

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?
 

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
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?
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
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?
 

Forum statistics

Threads
1,136,272
Messages
5,674,749
Members
419,525
Latest member
helensesc

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
Top