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

Hi Andrea

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


--

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


wrote in message ups.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

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

How many sheet you have in the workbooks ?
How do you want to name the sheets in the new workbook?

--

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


wrote in message ps.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

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

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 Bruin
http://www.rondebruin.nl/tips.htm


wrote in message oups.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