Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automate Combine 2 Spreadsheets into two worksheets
Hi - Is there any way to automate using a macro the procedure to combine two
separate workbooks into one workbook where the separate workbooks are now on separate worksheets? I know how to manually do this but need to automate this for our operators who will be running this process on a weekly bases. I'm capturing conditional data using MONARCH, exporting it out to a flat file, importing flat file into an ACCESS program slices & dices the information according to the requirements then exports it out into two separate workbooks. Since I'm trying to avoid these operators from having to open any files to manually manipulate the data (too time consuming - it produces over 20 files) I'm looking for other alteratives. I greatly appreciate any suggests. Thanks in advance Uni |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automate Combine 2 Spreadsheets into two worksheets
It is pretty simple to combine multiple workbooks into a single workbook, but
I need more info 1) Where do you want the macro to run? In one of the workbooks or from a completely seperate workbook? 2) What directory are the workbooks located? 3) What are the workbook names? 4) What are the worksheet names? Below is a general; purpose macro that will import all book and sheets into the workbook where the macro is run Sub combinebooks() Const folder = "c:\temp\test" First = True Do If First = True Then Filename = Dir(folder & "\*.xls") First = False Else Filename = Dir() End If If Filename < "" Then Workbooks.Open folder & "\" & Filename Set oldbook = ActiveWorkbook For Each sh In ActiveWorkbook.Sheets With ThisWorkbook sh.Copy after:=.Sheets(.Sheets.Count) End With Next sh oldbook.Close End If Loop While Filename < "" End Sub "Uni" wrote: Hi - Is there any way to automate using a macro the procedure to combine two separate workbooks into one workbook where the separate workbooks are now on separate worksheets? I know how to manually do this but need to automate this for our operators who will be running this process on a weekly bases. I'm capturing conditional data using MONARCH, exporting it out to a flat file, importing flat file into an ACCESS program slices & dices the information according to the requirements then exports it out into two separate workbooks. Since I'm trying to avoid these operators from having to open any files to manually manipulate the data (too time consuming - it produces over 20 files) I'm looking for other alteratives. I greatly appreciate any suggests. Thanks in advance Uni |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automate Combine 2 Spreadsheets into two worksheets
Joel;
Thanks so much for your response €“ heres the answers to the questions that you had: 1. Since the process over writes the workbooks, Im thinking that I would be in a completely separate workbook. 2. Ive created a €śstaging€ť area. It would reside on G:\CIO\Billing\FundSummary\Export 3. & 4. The workbook and associated worksheet names a 3. Workbook Name = AllFunds56Summary 4. Worksheet name for this one is ALL 3. Workbook Name = AllFunds56SummaryRED 4. Worksheet name for this one is ALLRED This is just one example. Ive never coded anything like your example so please forgive me if I ask ignorant questions. Would I need a separate macro to run all differently named workbooks & worksheets? I was €śplaying around€ť with this and was still rather puzzled to where the macro/coding resides. It looks as though you can either €śembed€ť the macro into the workbook or you can use a €śnew€ť one. Since the ACCESS export is throwing these workbooks out in the €śstaging€ť directory of €śexport€ť where the previous weeks workbooks would get overwritten, Im assuming that it would be best to create a separate worksheet for each combination. Does that make sense? Again, thanks so much Joel! /r, Uni "Joel" wrote: It is pretty simple to combine multiple workbooks into a single workbook, but I need more info 1) Where do you want the macro to run? In one of the workbooks or from a completely seperate workbook? 2) What directory are the workbooks located? 3) What are the workbook names? 4) What are the worksheet names? Below is a general; purpose macro that will import all book and sheets into the workbook where the macro is run Sub combinebooks() Const folder = "c:\temp\test" First = True Do If First = True Then Filename = Dir(folder & "\*.xls") First = False Else Filename = Dir() End If If Filename < "" Then Workbooks.Open folder & "\" & Filename Set oldbook = ActiveWorkbook For Each sh In ActiveWorkbook.Sheets With ThisWorkbook sh.Copy after:=.Sheets(.Sheets.Count) End With Next sh oldbook.Close End If Loop While Filename < "" End Sub "Uni" wrote: Hi - Is there any way to automate using a macro the procedure to combine two separate workbooks into one workbook where the separate workbooks are now on separate worksheets? I know how to manually do this but need to automate this for our operators who will be running this process on a weekly bases. I'm capturing conditional data using MONARCH, exporting it out to a flat file, importing flat file into an ACCESS program slices & dices the information according to the requirements then exports it out into two separate workbooks. Since I'm trying to avoid these operators from having to open any files to manually manipulate the data (too time consuming - it produces over 20 files) I'm looking for other alteratives. I greatly appreciate any suggests. Thanks in advance Uni |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automate Combine 2 Spreadsheets into two worksheets
Below is the macro you requested. I created a new file for the combined
workbook called AllFunds56SummaryAll.xls Here are the answers to your questions 1) Would I need a separate macro to run all differently named workbooks & worksheets? Answer:You can have multiple macros in the same workbook. Each macro will have a different name. 2) I was €śplaying around€ť with this and was still rather puzzled to where the macro/coding resides. It looks as though you can either €śembed€ť the macro into the workbook or you can use a €śnew€ť one. Since the ACCESS export is throwing these workbooks out in the €śstaging€ť directory of €śexport€ť where the previous weeks workbooks would get overwritten, Im assuming that it would be best to create a separate worksheet for each combination. Does that make sense? What you said make sense. Because Access will over-write the workbooks each week you can't have the macro in one of the two workbooks that access creates because you will loose the macro. I suggest that you create a workbook that is called combine.xls (or something similar) with my macro. Sub combinebooks() Const folder = "G:\CIO\Billing\FundSummary\Export\" Workbooks.Open Filename:= _ folder & "AllFunds56Summary.xls" 'the next instruction will create a newworkbook ActiveWorkbook.Sheets("ALL").Copy Set newbook = ActiveWorkbook Workbooks("AllFunds56Summary.xls").Close Workbooks.Open Filename:= _ folder & "AllFunds56SummaryRED.xls" With newbook 'using copy with after will not create new book 'the dot infornt of Sheets will put the worksheet into newbook ActiveWorkbook.Sheets("ALLRED").Copy after:=.Sheets(1) 'the dot infront of saveas will save the newbook .SaveAs Filename:=folder & "AllFunds56SummaryAll.xls" 'the dot infront of close will close newbook .Close End With Workbooks("AllFunds56SummaryRed.xls").Close End Sub "Uni" wrote: Joel; Thanks so much for your response €“ heres the answers to the questions that you had: 1. Since the process over writes the workbooks, Im thinking that I would be in a completely separate workbook. 2. Ive created a €śstaging€ť area. It would reside on G:\CIO\Billing\FundSummary\Export 3. & 4. The workbook and associated worksheet names a 3. Workbook Name = AllFunds56Summary 4. Worksheet name for this one is ALL 3. Workbook Name = AllFunds56SummaryRED 4. Worksheet name for this one is ALLRED This is just one example. Ive never coded anything like your example so please forgive me if I ask ignorant questions. Would I need a separate macro to run all differently named workbooks & worksheets? I was €śplaying around€ť with this and was still rather puzzled to where the macro/coding resides. It looks as though you can either €śembed€ť the macro into the workbook or you can use a €śnew€ť one. Since the ACCESS export is throwing these workbooks out in the €śstaging€ť directory of €śexport€ť where the previous weeks workbooks would get overwritten, Im assuming that it would be best to create a separate worksheet for each combination. Does that make sense? Again, thanks so much Joel! /r, Uni "Joel" wrote: It is pretty simple to combine multiple workbooks into a single workbook, but I need more info 1) Where do you want the macro to run? In one of the workbooks or from a completely seperate workbook? 2) What directory are the workbooks located? 3) What are the workbook names? 4) What are the worksheet names? Below is a general; purpose macro that will import all book and sheets into the workbook where the macro is run Sub combinebooks() Const folder = "c:\temp\test" First = True Do If First = True Then Filename = Dir(folder & "\*.xls") First = False Else Filename = Dir() End If If Filename < "" Then Workbooks.Open folder & "\" & Filename Set oldbook = ActiveWorkbook For Each sh In ActiveWorkbook.Sheets With ThisWorkbook sh.Copy after:=.Sheets(.Sheets.Count) End With Next sh oldbook.Close End If Loop While Filename < "" End Sub "Uni" wrote: Hi - Is there any way to automate using a macro the procedure to combine two separate workbooks into one workbook where the separate workbooks are now on separate worksheets? I know how to manually do this but need to automate this for our operators who will be running this process on a weekly bases. I'm capturing conditional data using MONARCH, exporting it out to a flat file, importing flat file into an ACCESS program slices & dices the information according to the requirements then exports it out into two separate workbooks. Since I'm trying to avoid these operators from having to open any files to manually manipulate the data (too time consuming - it produces over 20 files) I'm looking for other alteratives. I greatly appreciate any suggests. Thanks in advance Uni |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automate Combine 2 Spreadsheets into two worksheets
Thanks, Joel. I'll try this and get back with you.
Again, many thanks, Uni "Joel" wrote: Below is the macro you requested. I created a new file for the combined workbook called AllFunds56SummaryAll.xls Here are the answers to your questions 1) Would I need a separate macro to run all differently named workbooks & worksheets? Answer:You can have multiple macros in the same workbook. Each macro will have a different name. 2) I was €śplaying around€ť with this and was still rather puzzled to where the macro/coding resides. It looks as though you can either €śembed€ť the macro into the workbook or you can use a €śnew€ť one. Since the ACCESS export is throwing these workbooks out in the €śstaging€ť directory of €śexport€ť where the previous weeks workbooks would get overwritten, Im assuming that it would be best to create a separate worksheet for each combination. Does that make sense? What you said make sense. Because Access will over-write the workbooks each week you can't have the macro in one of the two workbooks that access creates because you will loose the macro. I suggest that you create a workbook that is called combine.xls (or something similar) with my macro. Sub combinebooks() Const folder = "G:\CIO\Billing\FundSummary\Export\" Workbooks.Open Filename:= _ folder & "AllFunds56Summary.xls" 'the next instruction will create a newworkbook ActiveWorkbook.Sheets("ALL").Copy Set newbook = ActiveWorkbook Workbooks("AllFunds56Summary.xls").Close Workbooks.Open Filename:= _ folder & "AllFunds56SummaryRED.xls" With newbook 'using copy with after will not create new book 'the dot infornt of Sheets will put the worksheet into newbook ActiveWorkbook.Sheets("ALLRED").Copy after:=.Sheets(1) 'the dot infront of saveas will save the newbook .SaveAs Filename:=folder & "AllFunds56SummaryAll.xls" 'the dot infront of close will close newbook .Close End With Workbooks("AllFunds56SummaryRed.xls").Close End Sub "Uni" wrote: Joel; Thanks so much for your response €“ heres the answers to the questions that you had: 1. Since the process over writes the workbooks, Im thinking that I would be in a completely separate workbook. 2. Ive created a €śstaging€ť area. It would reside on G:\CIO\Billing\FundSummary\Export 3. & 4. The workbook and associated worksheet names a 3. Workbook Name = AllFunds56Summary 4. Worksheet name for this one is ALL 3. Workbook Name = AllFunds56SummaryRED 4. Worksheet name for this one is ALLRED This is just one example. Ive never coded anything like your example so please forgive me if I ask ignorant questions. Would I need a separate macro to run all differently named workbooks & worksheets? I was €śplaying around€ť with this and was still rather puzzled to where the macro/coding resides. It looks as though you can either €śembed€ť the macro into the workbook or you can use a €śnew€ť one. Since the ACCESS export is throwing these workbooks out in the €śstaging€ť directory of €śexport€ť where the previous weeks workbooks would get overwritten, Im assuming that it would be best to create a separate worksheet for each combination. Does that make sense? Again, thanks so much Joel! /r, Uni "Joel" wrote: It is pretty simple to combine multiple workbooks into a single workbook, but I need more info 1) Where do you want the macro to run? In one of the workbooks or from a completely seperate workbook? 2) What directory are the workbooks located? 3) What are the workbook names? 4) What are the worksheet names? Below is a general; purpose macro that will import all book and sheets into the workbook where the macro is run Sub combinebooks() Const folder = "c:\temp\test" First = True Do If First = True Then Filename = Dir(folder & "\*.xls") First = False Else Filename = Dir() End If If Filename < "" Then Workbooks.Open folder & "\" & Filename Set oldbook = ActiveWorkbook For Each sh In ActiveWorkbook.Sheets With ThisWorkbook sh.Copy after:=.Sheets(.Sheets.Count) End With Next sh oldbook.Close End If Loop While Filename < "" End Sub "Uni" wrote: Hi - Is there any way to automate using a macro the procedure to combine two separate workbooks into one workbook where the separate workbooks are now on separate worksheets? I know how to manually do this but need to automate this for our operators who will be running this process on a weekly bases. I'm capturing conditional data using MONARCH, exporting it out to a flat file, importing flat file into an ACCESS program slices & dices the information according to the requirements then exports it out into two separate workbooks. Since I'm trying to avoid these operators from having to open any files to manually manipulate the data (too time consuming - it produces over 20 files) I'm looking for other alteratives. I greatly appreciate any suggests. Thanks in advance Uni |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automate Combine 2 Spreadsheets into two worksheets
Joel;
This worked GREAT!!! Thank you so very much. You're my hero:) Uni "Joel" wrote: Below is the macro you requested. I created a new file for the combined workbook called AllFunds56SummaryAll.xls Here are the answers to your questions 1) Would I need a separate macro to run all differently named workbooks & worksheets? Answer:You can have multiple macros in the same workbook. Each macro will have a different name. 2) I was €śplaying around€ť with this and was still rather puzzled to where the macro/coding resides. It looks as though you can either €śembed€ť the macro into the workbook or you can use a €śnew€ť one. Since the ACCESS export is throwing these workbooks out in the €śstaging€ť directory of €śexport€ť where the previous weeks workbooks would get overwritten, Im assuming that it would be best to create a separate worksheet for each combination. Does that make sense? What you said make sense. Because Access will over-write the workbooks each week you can't have the macro in one of the two workbooks that access creates because you will loose the macro. I suggest that you create a workbook that is called combine.xls (or something similar) with my macro. Sub combinebooks() Const folder = "G:\CIO\Billing\FundSummary\Export\" Workbooks.Open Filename:= _ folder & "AllFunds56Summary.xls" 'the next instruction will create a newworkbook ActiveWorkbook.Sheets("ALL").Copy Set newbook = ActiveWorkbook Workbooks("AllFunds56Summary.xls").Close Workbooks.Open Filename:= _ folder & "AllFunds56SummaryRED.xls" With newbook 'using copy with after will not create new book 'the dot infornt of Sheets will put the worksheet into newbook ActiveWorkbook.Sheets("ALLRED").Copy after:=.Sheets(1) 'the dot infront of saveas will save the newbook .SaveAs Filename:=folder & "AllFunds56SummaryAll.xls" 'the dot infront of close will close newbook .Close End With Workbooks("AllFunds56SummaryRed.xls").Close End Sub "Uni" wrote: Joel; Thanks so much for your response €“ heres the answers to the questions that you had: 1. Since the process over writes the workbooks, Im thinking that I would be in a completely separate workbook. 2. Ive created a €śstaging€ť area. It would reside on G:\CIO\Billing\FundSummary\Export 3. & 4. The workbook and associated worksheet names a 3. Workbook Name = AllFunds56Summary 4. Worksheet name for this one is ALL 3. Workbook Name = AllFunds56SummaryRED 4. Worksheet name for this one is ALLRED This is just one example. Ive never coded anything like your example so please forgive me if I ask ignorant questions. Would I need a separate macro to run all differently named workbooks & worksheets? I was €śplaying around€ť with this and was still rather puzzled to where the macro/coding resides. It looks as though you can either €śembed€ť the macro into the workbook or you can use a €śnew€ť one. Since the ACCESS export is throwing these workbooks out in the €śstaging€ť directory of €śexport€ť where the previous weeks workbooks would get overwritten, Im assuming that it would be best to create a separate worksheet for each combination. Does that make sense? Again, thanks so much Joel! /r, Uni "Joel" wrote: It is pretty simple to combine multiple workbooks into a single workbook, but I need more info 1) Where do you want the macro to run? In one of the workbooks or from a completely seperate workbook? 2) What directory are the workbooks located? 3) What are the workbook names? 4) What are the worksheet names? Below is a general; purpose macro that will import all book and sheets into the workbook where the macro is run Sub combinebooks() Const folder = "c:\temp\test" First = True Do If First = True Then Filename = Dir(folder & "\*.xls") First = False Else Filename = Dir() End If If Filename < "" Then Workbooks.Open folder & "\" & Filename Set oldbook = ActiveWorkbook For Each sh In ActiveWorkbook.Sheets With ThisWorkbook sh.Copy after:=.Sheets(.Sheets.Count) End With Next sh oldbook.Close End If Loop While Filename < "" End Sub "Uni" wrote: Hi - Is there any way to automate using a macro the procedure to combine two separate workbooks into one workbook where the separate workbooks are now on separate worksheets? I know how to manually do this but need to automate this for our operators who will be running this process on a weekly bases. I'm capturing conditional data using MONARCH, exporting it out to a flat file, importing flat file into an ACCESS program slices & dices the information according to the requirements then exports it out into two separate workbooks. Since I'm trying to avoid these operators from having to open any files to manually manipulate the data (too time consuming - it produces over 20 files) I'm looking for other alteratives. I greatly appreciate any suggests. Thanks in advance Uni |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to combine data from 2 spreadsheets | New Users to Excel | |||
How can I automate copying data from one into several spreadsheets | Excel Discussion (Misc queries) | |||
How do I combine spreadsheets? | Excel Discussion (Misc queries) | |||
How to automate footers (filename/date) for all new spreadsheets? | Excel Worksheet Functions | |||
Combine two spreadsheets using Macro | Excel Worksheet Functions |