See my reply with a new macro

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


wrote in message oups.com...
On May 10, 4:37 pm, "Ron de Bruin" wrote:
OK, here we go

Change a few things in my basic example

MyPath = "C:\Users\Ron\test"
Path of the folder with the files

Set DestWB = Workbooks.Open("C:\Users\Ron\template.xls")
Path and name of your template file

Application.Run "template.xls!MyMacroName"
Name of the template filer and the macro you want to run in this file

This is the save line
DestWB.SaveAs foldername & DestWB.Sheets("Products").Range("A1").Value & ".xls"
I think it is what you want, it save with the cell value of A1 in the sheet Products

Sub Example1()
Dim mybook As Workbook
Dim DestWB As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim oApp As Object
Dim oFolder
Dim foldername

SaveDriveDir = CurDir
MyPath = "C:\Users\Ron\test"

ChDrive MyPath
ChDir MyPath

FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Set oApp = CreateObject("Shell.Application")

Application.ScreenUpdating = False

Do While FNames < ""

'open the template file
Set DestWB = Workbooks.Open("C:\Users\Ron\template.xls")

Set mybook = Workbooks.Open(FNames)

'Copy sheets to your template workbook
mybook.Worksheets.Copy after:= _
DestWB.Sheets(DestWB.Sheets.Count)

'run the macro in your tempate file
Application.Run "template.xls!MyMacroName"

'Browse to the folder
Set oFolder = oApp.BrowseForFolder(0, "Select folder to save the files", 512)
If Not oFolder Is Nothing Then
foldername = oFolder.Self.Path
If Right(foldername, 1) < "\" Then
foldername = foldername & "\"
End If

Application.DisplayAlerts = False
DestWB.SaveAs foldername & DestWB.Sheets("Products").Range("A1").Value & ".xls"
Application.DisplayAlerts = True
End If

'close files
mybook.Close False
DestWB.Close False

'go to the next file
FNames = Dir()
Loop

ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



wrote in ooglegroups.com...
On 9 May, 23:03, wrote:
On 9 May, 22:28, "Ron de Bruin" wrote:

Ok, now I understand you.

Is it a real template (xlt) or a normal workbook that you use to copy the sheets in ?.

Bed time here now.
I will make a example for you tomorrow after work

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

wrote in oglegroups.com...
On 9 May, 21:38, "Ron de Bruin" wrote:
You can use this basic macro to collect all sheet in the folder "C:\Users\Ron\test"
Change it to your folder and run the macro in your template file

Post back if it is working and then we add the save code

Sub Example11()
Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Users\Ron\test"

ChDrive MyPath
ChDir MyPath

FNames = Dir("*.xlsx")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook

Do While FNames < ""
Set mybook = Workbooks.Open(FNames)

mybook.Worksheets.Copy after:= _
basebook.Sheets(basebook.Sheets.Count)

mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

wrote in ooglegroups.com...
On 9 May, 21:02, "Ron de Bruin" wrote:
How many sheet you have in the workbooks ?
How do you want to name the sheets in the new workbook?

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

wrote in glegroups.com...
On 9 May, 20:20, "Ron de Bruin" wrote:
Hi Andrea

Start with this page for a one sheet example or use the copy a range example from all
sheetshttp://www.rondebruin.nl/fso.htm

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

wrote in oglegroups.com...
Hi

I have multiple XLS files located in one folder.

Each week I have to manually go into the files and then copy all the
worksheets into my template.

I then run a macro in the template and when done save the file as a
name in the appropriate folder.

I repeat this for all my spreadsheets.

Is there a macro that can open all the spreadsheets in a particular
folder one by one then copy and paste all the sheets into another
spreadsheet which shall remain the same (it's a template),

I would then like the macro to call the macro in my template sheet
then save the spreadsheet in another location with the name of the
spreadsheet being the value in Cell A1 of the sheet "Products".

I really hope this makes sense to someone,

Thanks for your help,

Andrea- Hide quoted text -

- Show quoted text -

Hi Ron,

I have found out how to copy the sheets but I cannot find a macro to
meet my exact needs.....

I have say 10 files. I want them opened 1 by 1 individually and all
the sheets copied into my template file located in a particular folder
and then the macro within that template file to be run and then the
individual file saved in another folder. Then the macro should loop
and do the same thing with the next file in the folder,

Does this make sense?

I dont know how to perform the above tasks.

Thanks for the advice so far.

Andrea- Hide quoted text -

- Show quoted text -

Hi Ron,

There are five sheets in each of the workbooks however the macro which
runs within the template sheet requires the name to be the same hence
I felt it might be better if there is some sort of select all sheets
function??

then copy them into the template run the other macro within the
template save the file then move on to the next file.

Thank you for your help with this,

I'm too old to be doing these things but you are all very wonderful at
this sort of things.

Andrea- Hide quoted text -

- Show quoted text -

Hi Ron

I think this is not what I want,

All the sheets in the workbooks are of the same name......

Each workbook is for a different client and cannot be mixed...........

This new macro your helping me with should;

1. Open up file one located in my directory, select and copy all the
sheets.
2. Open the workbook which is located in another directory (this is
the template file) paste all the sheets from step 1 into this
template file.
3. Call the macro located in the template file to perform the tasks
within that file.
4. Save the file using the name of the cell in A1 of a sheet named
"Products". The directory of where it should be saved should be asked
for by the user (if possible) or specified in the macro.
5. The macro should then close that workbook and then repeat the
process for the next XLS file found in the folder from step 1 again
and so on and so forth.

These are the five steps I would expect to see.......

Thanks Ron,

Andrea- Hide quoted text -

- Show quoted text -

Hi Ron,

It's just a normal XLS file which we use to copy sheets and then run a
macro.

I would appreciate your help with the template,

What time do you finish work? I'm dependant on you now,

Thanks again

Andrea- Hide quoted text -

- Show quoted text -

Hi Ron,

If you need any further information from me then please let me know,

I hope I have covered everything with you,

I've seen how professional you have been at helping people....

I very much appreciate everything your doing for me,

Kind Regards,

Andrea- Hide quoted text -

- Show quoted text -

Hi Ron,

Did you get my last message about just being able to program where to
save the files rather than have to go through the explorer window? If
i could add this to the macro that would be great.

Please let me know if you can help with this,

Kind Regards,

Andrea