Perform task on multiple sheets in a folder

[amorrison2006]

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

 

[Ron de Bruin]

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

 

[amorrison2006]

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














- 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

 

[Ron de Bruin]

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

 

[amorrison2006]

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












- 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

 

[Ron de Bruin]

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

 

[amorrison2006]

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











- 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

 

[Ron de Bruin]

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

 

[amorrison2006]

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














- 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

 

[amorrison2006]

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

 

[Ron de Bruin]

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

 

[amorrison2006]

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












- Show quoted text -

You met my specifications perfectly!!!!!!

You are an absolute angel......

How could I put in the code to save to a specified folder rathern than
ask the user where to save?

This would be the only other thing I would be happy with.

Thanks Ron,

Andrea

 

[amorrison2006]

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












- 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

 

[Ron de Bruin]

Hi Andrea

Also Change this line in this new macro

SaveFolder = "C:\Data"

I not test it but this should work


Sub Example1New()
Dim mybook As Workbook
Dim DestWB As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim SaveFolder As String

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

SaveFolder = "C:\Data"

If Right(SaveFolder, 1) <> "\" Then
SaveFolder = SaveFolder & "\"
End If

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"


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

'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

 

[Ron de Bruin]

See my reply with a new macro

 

[amorrison2006]

See my reply with a new macro

--

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





...

read more »- Hide quoted text -

- Show quoted text -

Hi Ron

You must be earning millions for doing this!,

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

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

Thanks again,

Andrea

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

 

[Ron de Bruin]

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

See my reply with a new macro

--

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





...

read more »- Hide quoted text -

- Show quoted text -

Hi Ron

You must be earning millions for doing this!,

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

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

Thanks again,

Andrea

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

 

[amorrison2006]

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

 

[Ron de Bruin]

You can use this if you want to use range values

MyPath = Sheets("Sheet1").Range("A1").Value

You can use this also for the other things

--

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


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

 

[amorrison2006]

Hi Ron,

Since you were such an angel with the previous macro....I actually
thought the previous task was impossible to do but you managed to do
the impossible.

I wondered if when it had finished processeding each macro it open up
another spreadsheet and filled in the data and time within a range of
cells,

So in Column A I had the value of the previous sheet which contains
the name in Cells A1. I want the macro to look up the value from Cell
A1 of the template file and then search for this in column A of
another record sheet spreadsheet called "RecordSheet.xls" and then
next to this in Column B it will put the date and then in Column C the
time.

Do you get what I mean?

I just want a record of when the files were created to save me having
to enter the data into another sheet.

Please let me know if you need something else,

THanks so much

Andrea