Hi Ron,

How could I have the folders locations selected by look at Cells A1

I tried using MYPATH= Cells ("A1").Value for example and gave up
because it didnt work for me.

I would like th template file value selected from cells A2 of the
current file.


I also want the output placed a folder using the value from A3 so the
user doesnt have to open the macro and make any changes.

I have saved the entire macro in a file and named the file macro1.

I appreciate your help,

Many thanks,

Andrea

On 10 May, 19:16, "Ron de Bruin" wrote:
Hi Andrea

Suggestion: please reply above the thread.
Now everybody must scroll down to read your reply.

You must be earning millions for doing this!,

Nothing, it is my hobby

When it has finished processing all the files I am getting an error
message.

There is no error trapping in this macro
Maybe a file with no sheet Products or a empty cell A1
or characters in A1 that are not allowed in a file name

Could I perhaps receive a message telling me it has completed all the
files in the folder? Just to make it more user friendly.

After this line

Loop

you can add this line

MsgBox "Done with running this macro"

P.S Where have you been all my life???!!!

In the Netherlands <vbg

--

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

wrote in oglegroups.com...

On 10 May, 18:31, "Ron de Bruin" wrote:



See my reply with a new macro

--

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

wrote in ooglegroups.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

...

read more »- Hide quoted text -

- Show quoted text -