Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to split and consolidate sheets into new workbooks
At present I have three excel files, say 1, 2 &3 each with a tab relating to
a cost centre, say A, B & C as follows: 1 A B C 2 A B C 3 A B C What I would like to do is change the split so that I have a workbook for each cost centre containing the sheet from each file such as: A 1 2 3 B 1 2 3 C 1 2 3. Please can you someone advise if there is an automated way I can do this or else point me in the right direction? I think the hardest part might be to match the right cost centre to each new sheet? Also the number of cost centres varies over time. Many thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to split and consolidate sheets into new workbooks
Yes it is possible to automate this.
You need to somehow determine the number of files that you will be opening. Also, hopefully the cost centre code (A, B, & C) used on each worksheet tab is the same or at least A in workbook 1 is equal to A in workbook 2, though not necessary to be present in workbook 3. If that is true, then you will use one "program" to control the processes necessary to "transpose" your data. Although you do not need to perform all of the following actions in the sequence described, some common sense will be necessary. Obtain list of files. Open/Verify open the first file. Go through each worksheet and gather the name(s) of the cost centres. For each cost centre create a workbook. Add/copy the sheet of this first file to the new workbook. (If cells in the worksheet contain more than 255 characters, you will also need to then copy the contents of the worksheet in book 1 to your new workbook.) Rename the worksheet to indicate from which book it came. Open/Verify opened book 2. Collect the cost centre names. If a workbook does not exist for one of the cost centres, then create it. Add/copy the worksheet to the appropriate book (Either existing, or newly created.) Again deal with cells that have greater than 255 characters. Rename the new worksheet to indicate from which book it came. Repeat the above process until all workbooks have been read from, and all new workbooks have been created. After a little review of the above, it may make sense to simply add a worksheet to the new workbook, name it as the workbook from which data will be copied, then select all cells from the source worksheet and copy to the destination worksheet. This will prevent dealing with an error that appears in Excel when a worksheet is copied that has a cell with 255 characters. "Xluser@work" wrote: At present I have three excel files, say 1, 2 &3 each with a tab relating to a cost centre, say A, B & C as follows: 1 A B C 2 A B C 3 A B C What I would like to do is change the split so that I have a workbook for each cost centre containing the sheet from each file such as: A 1 2 3 B 1 2 3 C 1 2 3. Please can you someone advise if there is an automated way I can do this or else point me in the right direction? I think the hardest part might be to match the right cost centre to each new sheet? Also the number of cost centres varies over time. Many thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to split and consolidate sheets into new workbooks
Many thanks GB.
I understand exactly what you have suggested in laymans terms. My problem is more to do with converting this into code. Can you suggest a starting point to locate such code? Once I see a piece of code I am fairly good at changing to suit my particular needs but I am useless when it comes to writing from scratch! Many thanks "GB" wrote: Yes it is possible to automate this. You need to somehow determine the number of files that you will be opening. Also, hopefully the cost centre code (A, B, & C) used on each worksheet tab is the same or at least A in workbook 1 is equal to A in workbook 2, though not necessary to be present in workbook 3. If that is true, then you will use one "program" to control the processes necessary to "transpose" your data. Although you do not need to perform all of the following actions in the sequence described, some common sense will be necessary. Obtain list of files. Open/Verify open the first file. Go through each worksheet and gather the name(s) of the cost centres. For each cost centre create a workbook. Add/copy the sheet of this first file to the new workbook. (If cells in the worksheet contain more than 255 characters, you will also need to then copy the contents of the worksheet in book 1 to your new workbook.) Rename the worksheet to indicate from which book it came. Open/Verify opened book 2. Collect the cost centre names. If a workbook does not exist for one of the cost centres, then create it. Add/copy the worksheet to the appropriate book (Either existing, or newly created.) Again deal with cells that have greater than 255 characters. Rename the new worksheet to indicate from which book it came. Repeat the above process until all workbooks have been read from, and all new workbooks have been created. After a little review of the above, it may make sense to simply add a worksheet to the new workbook, name it as the workbook from which data will be copied, then select all cells from the source worksheet and copy to the destination worksheet. This will prevent dealing with an error that appears in Excel when a worksheet is copied that has a cell with 255 characters. "Xluser@work" wrote: At present I have three excel files, say 1, 2 &3 each with a tab relating to a cost centre, say A, B & C as follows: 1 A B C 2 A B C 3 A B C What I would like to do is change the split so that I have a workbook for each cost centre containing the sheet from each file such as: A 1 2 3 B 1 2 3 C 1 2 3. Please can you someone advise if there is an automated way I can do this or else point me in the right direction? I think the hardest part might be to match the right cost centre to each new sheet? Also the number of cost centres varies over time. Many thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to split and consolidate sheets into new workbooks
This piece of code takes all the spreadsheets in a lower directory called SOURCE and creates all the new spreadsheets in a Directory that must exist called target root - contains this code root\source - contains source spreadsheets and nothing else root\target - all new spreadsheets created here Option Explicit Sub reMix() Dim strSourcePath As String Dim strSource As String Dim strTarget As String Dim strFiles(1 To 50) As String Dim iFiles As Integer Dim i As Integer Dim wkbSource As Workbook Dim wkbTarget As Workbook Dim wksSource As Worksheet Dim wksTarget As Worksheet Dim wks As Worksheet Dim str As String strSourcePath = ThisWorkbook.Path & "\source\" strTarget = ThisWorkbook.Path & "\target\" str = Dir(strSourcePath & "*.xls") iFiles = 0 Do Until str = "" iFiles = iFiles + 1 strFiles(iFiles) = str str = Dir() Loop For i = 1 To iFiles strSource = strFiles(i) Set wkbSource = Workbooks.Open(strSourcePath & strSource) For Each wks In wkbSource.Worksheets() str = strTarget & wks.Name & ".xls" If Dir(str) = "" Then wks.Copy Set wkbTarget = ActiveWorkbook Set wksTarget = ActiveSheet wkbTarget.SaveAs str Else Set wkbTarget = Workbooks().Open(str) wks.Copy wkbTarget.Worksheets(1) Set wksTarget = ActiveSheet End If wksTarget.Name = Left(wkbSource.Name, Len(wkbSource.Name) - 4) Set wksTarget = Nothing wkbTarget.Close xlYes Set wkbTarget = Nothing Next wkbSource.Close xlNo Set wkbSource = Nothing Next MsgBox "done" End Sub you should have error trapping etc hope it helps -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=503262 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to split and consolidate sheets into new workbooks
A little hint. (Sorta' to cover up the fact that I don't have a *really* good
source to point you to.) If you record macros to do simple steps (i.e., one macro for each of the instructions provided) you can cut/copy the pieces together to do what you want done. So for example, if you record a macro of you opening a workbook, you can review the code and get an understanding of what is going on. If you record a macro of you copying one page to another document then you can see that as well. There are a few steps in which that methodology won't work, for example, creating something that will allow you to identify one/all of the files that you want to include... Although, if several documents are opened by using the Ctrl key then you will see the resulting code there also. Kinda' get the picture? I apologize for not being able to provide the time necessary to describe the code more fully. I do know that if you also look at the help that is provided when you are in VBA in regards to the functions worksheet.name, workbook.name, and copy, you should be on the way to full development. I think that I also forgot to include the logic that either does not overwrite an existing worksheet, or it updates it. (Depending on your circumstances.) You will learn a lot if you review the code that Excel creates and go through the help files for anything you do not understand, or even if you do understand it, it might help just to become more knowledgeable. Best of Luck. :) "Xluser@work" wrote: Many thanks GB. I understand exactly what you have suggested in laymans terms. My problem is more to do with converting this into code. Can you suggest a starting point to locate such code? Once I see a piece of code I am fairly good at changing to suit my particular needs but I am useless when it comes to writing from scratch! Many thanks "GB" wrote: Yes it is possible to automate this. You need to somehow determine the number of files that you will be opening. Also, hopefully the cost centre code (A, B, & C) used on each worksheet tab is the same or at least A in workbook 1 is equal to A in workbook 2, though not necessary to be present in workbook 3. If that is true, then you will use one "program" to control the processes necessary to "transpose" your data. Although you do not need to perform all of the following actions in the sequence described, some common sense will be necessary. Obtain list of files. Open/Verify open the first file. Go through each worksheet and gather the name(s) of the cost centres. For each cost centre create a workbook. Add/copy the sheet of this first file to the new workbook. (If cells in the worksheet contain more than 255 characters, you will also need to then copy the contents of the worksheet in book 1 to your new workbook.) Rename the worksheet to indicate from which book it came. Open/Verify opened book 2. Collect the cost centre names. If a workbook does not exist for one of the cost centres, then create it. Add/copy the worksheet to the appropriate book (Either existing, or newly created.) Again deal with cells that have greater than 255 characters. Rename the new worksheet to indicate from which book it came. Repeat the above process until all workbooks have been read from, and all new workbooks have been created. After a little review of the above, it may make sense to simply add a worksheet to the new workbook, name it as the workbook from which data will be copied, then select all cells from the source worksheet and copy to the destination worksheet. This will prevent dealing with an error that appears in Excel when a worksheet is copied that has a cell with 255 characters. "Xluser@work" wrote: At present I have three excel files, say 1, 2 &3 each with a tab relating to a cost centre, say A, B & C as follows: 1 A B C 2 A B C 3 A B C What I would like to do is change the split so that I have a workbook for each cost centre containing the sheet from each file such as: A 1 2 3 B 1 2 3 C 1 2 3. Please can you someone advise if there is an automated way I can do this or else point me in the right direction? I think the hardest part might be to match the right cost centre to each new sheet? Also the number of cost centres varies over time. Many thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to split and consolidate sheets into new workbooks
A commented version Option Explicit Sub reMix() Dim strSourcePath As String Dim strSource As String Dim strTarget As String Dim strFiles(1 To 50) As String Dim iFiles As Integer Dim i As Integer Dim wkbSource As Workbook Dim wkbTarget As Workbook Dim wksSource As Worksheet Dim wksTarget As Worksheet Dim wks As Worksheet Dim str As String 'these define where the files are being taken from and being put 'it is assumed that: ' - all excel files in the source folder will be used ' - the target folder is empty strSourcePath = ThisWorkbook.Path & "\source\" strTarget = ThisWorkbook.Path & "\target\" 'create an arry of all the excel files ' have to do this as preprocess so that I can test for existence of ' the target file using the dir function str = Dir(strSourcePath & "*.xls") iFiles = 0 Do Until str = "" iFiles = iFiles + 1 strFiles(iFiles) = str str = Dir() Loop For i = 1 To iFiles 'loop through the source files strSource = strFiles(i) Set wkbSource = Workbooks.Open(strSourcePath & strSource) For Each wks In wkbSource.Worksheets() 'loop through each worsheet in the source file str = strTarget & wks.Name & ".xls" 'use the sheet name to create an output file name If Dir(str) = "" Then 'see if the file exists. could do this with an error trap, but it is messier wks.Copy 'create a new book Set wkbTarget = ActiveWorkbook Set wksTarget = ActiveSheet wkbTarget.SaveAs str Else Set wkbTarget = Workbooks().Open(str) ' add sheet to old book wks.Copy wkbTarget.Worksheets(1) Set wksTarget = ActiveSheet End If 'name sheet with workbook name wksTarget.Name = Left(wkbSource.Name, Len(wkbSource.Name) - 4) Set wksTarget = Nothing wkbTarget.Close xlYes Set wkbTarget = Nothing Next wkbSource.Close xlNo Set wkbSource = Nothing Next MsgBox "done" End Sub -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=503262 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidate Several Workbooks into One | Excel Worksheet Functions | |||
Consolidate multiple workbooks | Excel Worksheet Functions | |||
consolidate 2 different workbooks | Excel Discussion (Misc queries) | |||
Consolidate different sheets to different workbooks | Excel Worksheet Functions | |||
Consolidate data from several workbooks | Excel Worksheet Functions |