Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 Bruin http://www.rondebruin.nl/tips.htm wrote in message ups.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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 Bruin http://www.rondebruin.nl/tips.htm wrote in message oups.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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 - 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 -- 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 - 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 messagenews:1178741968..356444.236250@e51g2000hsg. googlegroups.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 - ... 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???!!! |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 wrote in message ups.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 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 - ... 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???!!! |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 - |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 wrote in message oups.com... 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 - |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perform task on multiple sheets in a folder
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 On 11 May, 20:26, "Ron de Bruin" wrote: 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 Bruinhttp://www.rondebruin.nl/tips.htm wrote in ooglegroups.com... 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 messagenews:1178817815.668780..166100@p77g2000hsh. googlegroups.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 ... read more »- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Perform a task if result is a whole number | Excel Programming | |||
perform calculations in 2 sheets | Excel Worksheet Functions | |||
Perform code on all (closed) workbooks scattered across sub-folders of common parent folder | Excel Programming | |||
linking an excel document to my task folder in outlook | Excel Discussion (Misc queries) | |||
Using a macro to perform a TASK on another workbook | Excel Programming |