Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split worksheet into multiple worksheets
I need to split a worksheet into multiple worksheets. The report pulled from
Dynamics creates one excel worksheet for multiple cost centres. I need to split the report so that every cost centre is on a different worksheet. The reports have different lenghts so I cannot make use of a "every 100th line" approach. The common denominator is that when a new report starts column A has a value with 2 alphabetical characters and 2 numerical characters. Can I somehow make use of this to indicate to excel to split the report when it encounters a value with 2 alphabetical characters and 2 numerical characters in Column A? Example: REPORT HEADING HO13 data data data etc REPORT HEADING LB17 data data etc The result I require is that HO13 will remain on the current worksheet and LB17 will move to a new worksheet (including his report heading). Please keep in mind that I might have more than 2 cost centres on the report (this will also vary with every report generated). If anybody could be of assistance I would greatly appreciate. Please provide the explanation for dummies. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split worksheet into multiple worksheets
Marica
Few queries 1. If you have headers repeated for each cost centre, why dont you split by the header rather than 2alpha/2numeric entry in Column A.. OR do you forsee any issues 2. Report spans to which column..(not really needed but still) 3. Do you need the formatting Or just the data in the new sheets.. If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: I need to split a worksheet into multiple worksheets. The report pulled from Dynamics creates one excel worksheet for multiple cost centres. I need to split the report so that every cost centre is on a different worksheet. The reports have different lenghts so I cannot make use of a "every 100th line" approach. The common denominator is that when a new report starts column A has a value with 2 alphabetical characters and 2 numerical characters. Can I somehow make use of this to indicate to excel to split the report when it encounters a value with 2 alphabetical characters and 2 numerical characters in Column A? Example: REPORT HEADING HO13 data data data etc REPORT HEADING LB17 data data etc The result I require is that HO13 will remain on the current worksheet and LB17 will move to a new worksheet (including his report heading). Please keep in mind that I might have more than 2 cost centres on the report (this will also vary with every report generated). If anybody could be of assistance I would greatly appreciate. Please provide the explanation for dummies. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split worksheet into multiple worksheets
Jacob, To answer your queries: 1. When the report pulls from Dynamics it adds a header in excel for every page that existed in the Dynamics report. This results in multiple headings that inbedded inbetween the data. So in instances where the report spans 5 pages there will be 5 headings eventhough I only have 2 cost centres. 2. The report spans to Column M. 3. The report pulls the formatting from Dynamics so I don't require any formatting. Except if it is possible to get rid of all the additional headers without the "manual delete" :-) Thanks for your response. "Jacob Skaria" wrote: Marica Few queries 1. If you have headers repeated for each cost centre, why dont you split by the header rather than 2alpha/2numeric entry in Column A.. OR do you forsee any issues 2. Report spans to which column..(not really needed but still) 3. Do you need the formatting Or just the data in the new sheets.. If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: I need to split a worksheet into multiple worksheets. The report pulled from Dynamics creates one excel worksheet for multiple cost centres. I need to split the report so that every cost centre is on a different worksheet. The reports have different lenghts so I cannot make use of a "every 100th line" approach. The common denominator is that when a new report starts column A has a value with 2 alphabetical characters and 2 numerical characters. Can I somehow make use of this to indicate to excel to split the report when it encounters a value with 2 alphabetical characters and 2 numerical characters in Column A? Example: REPORT HEADING HO13 data data data etc REPORT HEADING LB17 data data etc The result I require is that HO13 will remain on the current worksheet and LB17 will move to a new worksheet (including his report heading). Please keep in mind that I might have more than 2 cost centres on the report (this will also vary with every report generated). If anybody could be of assistance I would greatly appreciate. Please provide the explanation for dummies. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split worksheet into multiple worksheets
Take a backup of your actual data and test the below macro with your data in
Sheet1.The macro works on the active sheet..I assume your first cost centre is in Row2 (just below the header row). Sub Macro() Dim lngRow As Long, lngLastRow As Long, varTemp As Variant lngRow = 3 lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Do While lngRow <= lngLastRow If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then varTemp = Rows(lngRow - 1 & ":" & lngLastRow) Rows(lngRow - 1 & ":" & lngLastRow).ClearContents Sheets.Add After:=ActiveSheet Rows(1 & ":" & lngLastRow - lngRow + 2) = varTemp lngRow = 3 lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row End If lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: Jacob, To answer your queries: 1. When the report pulls from Dynamics it adds a header in excel for every page that existed in the Dynamics report. This results in multiple headings that inbedded inbetween the data. So in instances where the report spans 5 pages there will be 5 headings eventhough I only have 2 cost centres. 2. The report spans to Column M. 3. The report pulls the formatting from Dynamics so I don't require any formatting. Except if it is possible to get rid of all the additional headers without the "manual delete" :-) Thanks for your response. "Jacob Skaria" wrote: Marica Few queries 1. If you have headers repeated for each cost centre, why dont you split by the header rather than 2alpha/2numeric entry in Column A.. OR do you forsee any issues 2. Report spans to which column..(not really needed but still) 3. Do you need the formatting Or just the data in the new sheets.. If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: I need to split a worksheet into multiple worksheets. The report pulled from Dynamics creates one excel worksheet for multiple cost centres. I need to split the report so that every cost centre is on a different worksheet. The reports have different lenghts so I cannot make use of a "every 100th line" approach. The common denominator is that when a new report starts column A has a value with 2 alphabetical characters and 2 numerical characters. Can I somehow make use of this to indicate to excel to split the report when it encounters a value with 2 alphabetical characters and 2 numerical characters in Column A? Example: REPORT HEADING HO13 data data data etc REPORT HEADING LB17 data data etc The result I require is that HO13 will remain on the current worksheet and LB17 will move to a new worksheet (including his report heading). Please keep in mind that I might have more than 2 cost centres on the report (this will also vary with every report generated). If anybody could be of assistance I would greatly appreciate. Please provide the explanation for dummies. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split worksheet into multiple worksheets
Thanks Jacob, It almost worked. The report I tested it on consisted of 3 cost centres. It split the first 2 perfectly and it pulled the header of the 3rd into a new sheet, but it left the transactional data on the original sheet. Also, I should probably have mentioned that the header line consists of the first 7 lines in the report. "Jacob Skaria" wrote: Take a backup of your actual data and test the below macro with your data in Sheet1.The macro works on the active sheet..I assume your first cost centre is in Row2 (just below the header row). Sub Macro() Dim lngRow As Long, lngLastRow As Long, varTemp As Variant lngRow = 3 lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Do While lngRow <= lngLastRow If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then varTemp = Rows(lngRow - 1 & ":" & lngLastRow) Rows(lngRow - 1 & ":" & lngLastRow).ClearContents Sheets.Add After:=ActiveSheet Rows(1 & ":" & lngLastRow - lngRow + 2) = varTemp lngRow = 3 lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row End If lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: Jacob, To answer your queries: 1. When the report pulls from Dynamics it adds a header in excel for every page that existed in the Dynamics report. This results in multiple headings that inbedded inbetween the data. So in instances where the report spans 5 pages there will be 5 headings eventhough I only have 2 cost centres. 2. The report spans to Column M. 3. The report pulls the formatting from Dynamics so I don't require any formatting. Except if it is possible to get rid of all the additional headers without the "manual delete" :-) Thanks for your response. "Jacob Skaria" wrote: Marica Few queries 1. If you have headers repeated for each cost centre, why dont you split by the header rather than 2alpha/2numeric entry in Column A.. OR do you forsee any issues 2. Report spans to which column..(not really needed but still) 3. Do you need the formatting Or just the data in the new sheets.. If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: I need to split a worksheet into multiple worksheets. The report pulled from Dynamics creates one excel worksheet for multiple cost centres. I need to split the report so that every cost centre is on a different worksheet. The reports have different lenghts so I cannot make use of a "every 100th line" approach. The common denominator is that when a new report starts column A has a value with 2 alphabetical characters and 2 numerical characters. Can I somehow make use of this to indicate to excel to split the report when it encounters a value with 2 alphabetical characters and 2 numerical characters in Column A? Example: REPORT HEADING HO13 data data data etc REPORT HEADING LB17 data data etc The result I require is that HO13 will remain on the current worksheet and LB17 will move to a new worksheet (including his report heading). Please keep in mind that I might have more than 2 cost centres on the report (this will also vary with every report generated). If anybody could be of assistance I would greatly appreciate. Please provide the explanation for dummies. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split worksheet into multiple worksheets
If you mean to say all headers are in 7 lines; Adjust the below 3 lines in the code for 7 header lines varTemp = Rows(lngRow - 6 & ":" & lngLastRow) Rows(lngRow - 6 & ":" & lngLastRow).ClearContents Rows(1 & ":" & lngLastRow - lngRow + 8) = varTemp I have modified the earlier code a bit... Sub Macro() Dim lngRow As Long, lngLastRow As Long, varTemp As Variant Dim intCount As Integer lngRow = 1 lngLastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row Do While lngRow <= lngLastRow If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then intCount = intCount + 1 If intCount = 2 Then varTemp = Rows(lngRow - 1 & ":" & lngLastRow) Rows(lngRow - 1 & ":" & lngLastRow).ClearContents Sheets.Add After:=ActiveSheet Rows(1 & ":" & lngLastRow - lngRow + 2) = varTemp lngRow = 1 intCount = 0 lngLastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row End If End If lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: Thanks Jacob, It almost worked. The report I tested it on consisted of 3 cost centres. It split the first 2 perfectly and it pulled the header of the 3rd into a new sheet, but it left the transactional data on the original sheet. Also, I should probably have mentioned that the header line consists of the first 7 lines in the report. "Jacob Skaria" wrote: Take a backup of your actual data and test the below macro with your data in Sheet1.The macro works on the active sheet..I assume your first cost centre is in Row2 (just below the header row). Sub Macro() Dim lngRow As Long, lngLastRow As Long, varTemp As Variant lngRow = 3 lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Do While lngRow <= lngLastRow If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then varTemp = Rows(lngRow - 1 & ":" & lngLastRow) Rows(lngRow - 1 & ":" & lngLastRow).ClearContents Sheets.Add After:=ActiveSheet Rows(1 & ":" & lngLastRow - lngRow + 2) = varTemp lngRow = 3 lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row End If lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: Jacob, To answer your queries: 1. When the report pulls from Dynamics it adds a header in excel for every page that existed in the Dynamics report. This results in multiple headings that inbedded inbetween the data. So in instances where the report spans 5 pages there will be 5 headings eventhough I only have 2 cost centres. 2. The report spans to Column M. 3. The report pulls the formatting from Dynamics so I don't require any formatting. Except if it is possible to get rid of all the additional headers without the "manual delete" :-) Thanks for your response. "Jacob Skaria" wrote: Marica Few queries 1. If you have headers repeated for each cost centre, why dont you split by the header rather than 2alpha/2numeric entry in Column A.. OR do you forsee any issues 2. Report spans to which column..(not really needed but still) 3. Do you need the formatting Or just the data in the new sheets.. If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: I need to split a worksheet into multiple worksheets. The report pulled from Dynamics creates one excel worksheet for multiple cost centres. I need to split the report so that every cost centre is on a different worksheet. The reports have different lenghts so I cannot make use of a "every 100th line" approach. The common denominator is that when a new report starts column A has a value with 2 alphabetical characters and 2 numerical characters. Can I somehow make use of this to indicate to excel to split the report when it encounters a value with 2 alphabetical characters and 2 numerical characters in Column A? Example: REPORT HEADING HO13 data data data etc REPORT HEADING LB17 data data etc The result I require is that HO13 will remain on the current worksheet and LB17 will move to a new worksheet (including his report heading). Please keep in mind that I might have more than 2 cost centres on the report (this will also vary with every report generated). If anybody could be of assistance I would greatly appreciate. Please provide the explanation for dummies. Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split worksheet into multiple worksheets
I am getting a runtime error on line 13 - type mismatch:
If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then "Jacob Skaria" wrote: If you mean to say all headers are in 7 lines; Adjust the below 3 lines in the code for 7 header lines varTemp = Rows(lngRow - 6 & ":" & lngLastRow) Rows(lngRow - 6 & ":" & lngLastRow).ClearContents Rows(1 & ":" & lngLastRow - lngRow + 8) = varTemp I have modified the earlier code a bit... Sub Macro() Dim lngRow As Long, lngLastRow As Long, varTemp As Variant Dim intCount As Integer lngRow = 1 lngLastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row Do While lngRow <= lngLastRow If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then intCount = intCount + 1 If intCount = 2 Then varTemp = Rows(lngRow - 1 & ":" & lngLastRow) Rows(lngRow - 1 & ":" & lngLastRow).ClearContents Sheets.Add After:=ActiveSheet Rows(1 & ":" & lngLastRow - lngRow + 2) = varTemp lngRow = 1 intCount = 0 lngLastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row End If End If lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: Thanks Jacob, It almost worked. The report I tested it on consisted of 3 cost centres. It split the first 2 perfectly and it pulled the header of the 3rd into a new sheet, but it left the transactional data on the original sheet. Also, I should probably have mentioned that the header line consists of the first 7 lines in the report. "Jacob Skaria" wrote: Take a backup of your actual data and test the below macro with your data in Sheet1.The macro works on the active sheet..I assume your first cost centre is in Row2 (just below the header row). Sub Macro() Dim lngRow As Long, lngLastRow As Long, varTemp As Variant lngRow = 3 lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Do While lngRow <= lngLastRow If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then varTemp = Rows(lngRow - 1 & ":" & lngLastRow) Rows(lngRow - 1 & ":" & lngLastRow).ClearContents Sheets.Add After:=ActiveSheet Rows(1 & ":" & lngLastRow - lngRow + 2) = varTemp lngRow = 3 lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row End If lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: Jacob, To answer your queries: 1. When the report pulls from Dynamics it adds a header in excel for every page that existed in the Dynamics report. This results in multiple headings that inbedded inbetween the data. So in instances where the report spans 5 pages there will be 5 headings eventhough I only have 2 cost centres. 2. The report spans to Column M. 3. The report pulls the formatting from Dynamics so I don't require any formatting. Except if it is possible to get rid of all the additional headers without the "manual delete" :-) Thanks for your response. "Jacob Skaria" wrote: Marica Few queries 1. If you have headers repeated for each cost centre, why dont you split by the header rather than 2alpha/2numeric entry in Column A.. OR do you forsee any issues 2. Report spans to which column..(not really needed but still) 3. Do you need the formatting Or just the data in the new sheets.. If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: I need to split a worksheet into multiple worksheets. The report pulled from Dynamics creates one excel worksheet for multiple cost centres. I need to split the report so that every cost centre is on a different worksheet. The reports have different lenghts so I cannot make use of a "every 100th line" approach. The common denominator is that when a new report starts column A has a value with 2 alphabetical characters and 2 numerical characters. Can I somehow make use of this to indicate to excel to split the report when it encounters a value with 2 alphabetical characters and 2 numerical characters in Column A? Example: REPORT HEADING HO13 data data data etc REPORT HEADING LB17 data data etc The result I require is that HO13 will remain on the current worksheet and LB17 will move to a new worksheet (including his report heading). Please keep in mind that I might have more than 2 cost centres on the report (this will also vary with every report generated). If anybody could be of assistance I would greatly appreciate. Please provide the explanation for dummies. Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split worksheet into multiple worksheets
"7 header lines" is this only for the 1st header or for all headers...Post back with the first twenty lines of your data. (Just colA and B will do..) If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: I am getting a runtime error on line 13 - type mismatch: If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then "Jacob Skaria" wrote: If you mean to say all headers are in 7 lines; Adjust the below 3 lines in the code for 7 header lines varTemp = Rows(lngRow - 6 & ":" & lngLastRow) Rows(lngRow - 6 & ":" & lngLastRow).ClearContents Rows(1 & ":" & lngLastRow - lngRow + 8) = varTemp I have modified the earlier code a bit... Sub Macro() Dim lngRow As Long, lngLastRow As Long, varTemp As Variant Dim intCount As Integer lngRow = 1 lngLastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row Do While lngRow <= lngLastRow If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then intCount = intCount + 1 If intCount = 2 Then varTemp = Rows(lngRow - 1 & ":" & lngLastRow) Rows(lngRow - 1 & ":" & lngLastRow).ClearContents Sheets.Add After:=ActiveSheet Rows(1 & ":" & lngLastRow - lngRow + 2) = varTemp lngRow = 1 intCount = 0 lngLastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row End If End If lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: Thanks Jacob, It almost worked. The report I tested it on consisted of 3 cost centres. It split the first 2 perfectly and it pulled the header of the 3rd into a new sheet, but it left the transactional data on the original sheet. Also, I should probably have mentioned that the header line consists of the first 7 lines in the report. "Jacob Skaria" wrote: Take a backup of your actual data and test the below macro with your data in Sheet1.The macro works on the active sheet..I assume your first cost centre is in Row2 (just below the header row). Sub Macro() Dim lngRow As Long, lngLastRow As Long, varTemp As Variant lngRow = 3 lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Do While lngRow <= lngLastRow If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then varTemp = Rows(lngRow - 1 & ":" & lngLastRow) Rows(lngRow - 1 & ":" & lngLastRow).ClearContents Sheets.Add After:=ActiveSheet Rows(1 & ":" & lngLastRow - lngRow + 2) = varTemp lngRow = 3 lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row End If lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: Jacob, To answer your queries: 1. When the report pulls from Dynamics it adds a header in excel for every page that existed in the Dynamics report. This results in multiple headings that inbedded inbetween the data. So in instances where the report spans 5 pages there will be 5 headings eventhough I only have 2 cost centres. 2. The report spans to Column M. 3. The report pulls the formatting from Dynamics so I don't require any formatting. Except if it is possible to get rid of all the additional headers without the "manual delete" :-) Thanks for your response. "Jacob Skaria" wrote: Marica Few queries 1. If you have headers repeated for each cost centre, why dont you split by the header rather than 2alpha/2numeric entry in Column A.. OR do you forsee any issues 2. Report spans to which column..(not really needed but still) 3. Do you need the formatting Or just the data in the new sheets.. If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: I need to split a worksheet into multiple worksheets. The report pulled from Dynamics creates one excel worksheet for multiple cost centres. I need to split the report so that every cost centre is on a different worksheet. The reports have different lenghts so I cannot make use of a "every 100th line" approach. The common denominator is that when a new report starts column A has a value with 2 alphabetical characters and 2 numerical characters. Can I somehow make use of this to indicate to excel to split the report when it encounters a value with 2 alphabetical characters and 2 numerical characters in Column A? Example: REPORT HEADING HO13 data data data etc REPORT HEADING LB17 data data etc The result I require is that HO13 will remain on the current worksheet and LB17 will move to a new worksheet (including his report heading). Please keep in mind that I might have more than 2 cost centres on the report (this will also vary with every report generated). If anybody could be of assistance I would greatly appreciate. Please provide the explanation for dummies. Thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split worksheet into multiple worksheets
"7 header lines" is for all headers. It basically repeat the top header everytime it reads a new page from the original report (this might not be a new page on excel though) - As below, it will reflect from Cape Town Centre to Cost Centre Code for every instance. I copied colA - C since the only data contained in A and B is header and cost centre info. Cape Town Centre INCOME STATEMENT PER COST CENTRE Cost Centre Code Cost Centre Name HO83 Cape Centre INCOME TOURISM NON TOURISM INCOME PROPERTY RIGHTS FUNDING OTHER INCOME The empty lines in the header contains the date, time and page numbers which are reflecting in column M. The rest of the empty lines are formatting. "Jacob Skaria" wrote: "7 header lines" is this only for the 1st header or for all headers...Post back with the first twenty lines of your data. (Just colA and B will do..) If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: I am getting a runtime error on line 13 - type mismatch: If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then "Jacob Skaria" wrote: If you mean to say all headers are in 7 lines; Adjust the below 3 lines in the code for 7 header lines varTemp = Rows(lngRow - 6 & ":" & lngLastRow) Rows(lngRow - 6 & ":" & lngLastRow).ClearContents Rows(1 & ":" & lngLastRow - lngRow + 8) = varTemp I have modified the earlier code a bit... Sub Macro() Dim lngRow As Long, lngLastRow As Long, varTemp As Variant Dim intCount As Integer lngRow = 1 lngLastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row Do While lngRow <= lngLastRow If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then intCount = intCount + 1 If intCount = 2 Then varTemp = Rows(lngRow - 1 & ":" & lngLastRow) Rows(lngRow - 1 & ":" & lngLastRow).ClearContents Sheets.Add After:=ActiveSheet Rows(1 & ":" & lngLastRow - lngRow + 2) = varTemp lngRow = 1 intCount = 0 lngLastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row End If End If lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: Thanks Jacob, It almost worked. The report I tested it on consisted of 3 cost centres. It split the first 2 perfectly and it pulled the header of the 3rd into a new sheet, but it left the transactional data on the original sheet. Also, I should probably have mentioned that the header line consists of the first 7 lines in the report. "Jacob Skaria" wrote: Take a backup of your actual data and test the below macro with your data in Sheet1.The macro works on the active sheet..I assume your first cost centre is in Row2 (just below the header row). Sub Macro() Dim lngRow As Long, lngLastRow As Long, varTemp As Variant lngRow = 3 lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Do While lngRow <= lngLastRow If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then varTemp = Rows(lngRow - 1 & ":" & lngLastRow) Rows(lngRow - 1 & ":" & lngLastRow).ClearContents Sheets.Add After:=ActiveSheet Rows(1 & ":" & lngLastRow - lngRow + 2) = varTemp lngRow = 3 lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row End If lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: Jacob, To answer your queries: 1. When the report pulls from Dynamics it adds a header in excel for every page that existed in the Dynamics report. This results in multiple headings that inbedded inbetween the data. So in instances where the report spans 5 pages there will be 5 headings eventhough I only have 2 cost centres. 2. The report spans to Column M. 3. The report pulls the formatting from Dynamics so I don't require any formatting. Except if it is possible to get rid of all the additional headers without the "manual delete" :-) Thanks for your response. "Jacob Skaria" wrote: Marica Few queries 1. If you have headers repeated for each cost centre, why dont you split by the header rather than 2alpha/2numeric entry in Column A.. OR do you forsee any issues 2. Report spans to which column..(not really needed but still) 3. Do you need the formatting Or just the data in the new sheets.. If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: I need to split a worksheet into multiple worksheets. The report pulled from Dynamics creates one excel worksheet for multiple cost centres. I need to split the report so that every cost centre is on a different worksheet. The reports have different lenghts so I cannot make use of a "every 100th line" approach. The common denominator is that when a new report starts column A has a value with 2 alphabetical characters and 2 numerical characters. Can I somehow make use of this to indicate to excel to split the report when it encounters a value with 2 alphabetical characters and 2 numerical characters in Column A? Example: REPORT HEADING HO13 data data data etc REPORT HEADING LB17 data data etc The result I require is that HO13 will remain on the current worksheet and LB17 will move to a new worksheet (including his report heading). Please keep in mind that I might have more than 2 cost centres on the report (this will also vary with every report generated). If anybody could be of assistance I would greatly appreciate. Please provide the explanation for dummies. Thanks |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split worksheet into multiple worksheets
The below should work... Sub Macro() Dim lngRow As Long, lngLastRow As Long, varTemp As Variant Dim intCount As Integer lngRow = 1 lngLastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row Do While lngRow <= lngLastRow If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then intCount = intCount + 1 If intCount = 2 Then varTemp = Rows(lngRow - 7 & ":" & lngLastRow) Rows(lngRow - 7 & ":" & lngLastRow).ClearContents Sheets.Add After:=ActiveSheet Rows(1 & ":" & lngLastRow - lngRow + 8) = varTemp lngRow = 1 intCount = 0 lngLastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row End If End If lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: "7 header lines" is for all headers. It basically repeat the top header everytime it reads a new page from the original report (this might not be a new page on excel though) - As below, it will reflect from Cape Town Centre to Cost Centre Code for every instance. I copied colA - C since the only data contained in A and B is header and cost centre info. Cape Town Centre INCOME STATEMENT PER COST CENTRE Cost Centre Code Cost Centre Name HO83 Cape Centre INCOME TOURISM NON TOURISM INCOME PROPERTY RIGHTS FUNDING OTHER INCOME The empty lines in the header contains the date, time and page numbers which are reflecting in column M. The rest of the empty lines are formatting. "Jacob Skaria" wrote: "7 header lines" is this only for the 1st header or for all headers...Post back with the first twenty lines of your data. (Just colA and B will do..) If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: I am getting a runtime error on line 13 - type mismatch: If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then "Jacob Skaria" wrote: If you mean to say all headers are in 7 lines; Adjust the below 3 lines in the code for 7 header lines varTemp = Rows(lngRow - 6 & ":" & lngLastRow) Rows(lngRow - 6 & ":" & lngLastRow).ClearContents Rows(1 & ":" & lngLastRow - lngRow + 8) = varTemp I have modified the earlier code a bit... Sub Macro() Dim lngRow As Long, lngLastRow As Long, varTemp As Variant Dim intCount As Integer lngRow = 1 lngLastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row Do While lngRow <= lngLastRow If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then intCount = intCount + 1 If intCount = 2 Then varTemp = Rows(lngRow - 1 & ":" & lngLastRow) Rows(lngRow - 1 & ":" & lngLastRow).ClearContents Sheets.Add After:=ActiveSheet Rows(1 & ":" & lngLastRow - lngRow + 2) = varTemp lngRow = 1 intCount = 0 lngLastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row End If End If lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: Thanks Jacob, It almost worked. The report I tested it on consisted of 3 cost centres. It split the first 2 perfectly and it pulled the header of the 3rd into a new sheet, but it left the transactional data on the original sheet. Also, I should probably have mentioned that the header line consists of the first 7 lines in the report. "Jacob Skaria" wrote: Take a backup of your actual data and test the below macro with your data in Sheet1.The macro works on the active sheet..I assume your first cost centre is in Row2 (just below the header row). Sub Macro() Dim lngRow As Long, lngLastRow As Long, varTemp As Variant lngRow = 3 lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Do While lngRow <= lngLastRow If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then varTemp = Rows(lngRow - 1 & ":" & lngLastRow) Rows(lngRow - 1 & ":" & lngLastRow).ClearContents Sheets.Add After:=ActiveSheet Rows(1 & ":" & lngLastRow - lngRow + 2) = varTemp lngRow = 3 lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row End If lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: Jacob, To answer your queries: 1. When the report pulls from Dynamics it adds a header in excel for every page that existed in the Dynamics report. This results in multiple headings that inbedded inbetween the data. So in instances where the report spans 5 pages there will be 5 headings eventhough I only have 2 cost centres. 2. The report spans to Column M. 3. The report pulls the formatting from Dynamics so I don't require any formatting. Except if it is possible to get rid of all the additional headers without the "manual delete" :-) Thanks for your response. "Jacob Skaria" wrote: Marica Few queries 1. If you have headers repeated for each cost centre, why dont you split by the header rather than 2alpha/2numeric entry in Column A.. OR do you forsee any issues 2. Report spans to which column..(not really needed but still) 3. Do you need the formatting Or just the data in the new sheets.. If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: I need to split a worksheet into multiple worksheets. The report pulled from Dynamics creates one excel worksheet for multiple cost centres. I need to split the report so that every cost centre is on a different worksheet. The reports have different lenghts so I cannot make use of a "every 100th line" approach. The common denominator is that when a new report starts column A has a value with 2 alphabetical characters and 2 numerical characters. Can I somehow make use of this to indicate to excel to split the report when it encounters a value with 2 alphabetical characters and 2 numerical characters in Column A? Example: REPORT HEADING HO13 data data data etc REPORT HEADING LB17 data data etc The result I require is that HO13 will remain on the current worksheet and LB17 will move to a new worksheet (including his report heading). Please keep in mind that I might have more than 2 cost centres on the report (this will also vary with every report generated). If anybody could be of assistance I would greatly appreciate. Please provide the explanation for dummies. Thanks |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split worksheet into multiple worksheets
It worked! Thank you very much! You've been a great help! "Jacob Skaria" wrote: The below should work... Sub Macro() Dim lngRow As Long, lngLastRow As Long, varTemp As Variant Dim intCount As Integer lngRow = 1 lngLastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row Do While lngRow <= lngLastRow If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then intCount = intCount + 1 If intCount = 2 Then varTemp = Rows(lngRow - 7 & ":" & lngLastRow) Rows(lngRow - 7 & ":" & lngLastRow).ClearContents Sheets.Add After:=ActiveSheet Rows(1 & ":" & lngLastRow - lngRow + 8) = varTemp lngRow = 1 intCount = 0 lngLastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row End If End If lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: "7 header lines" is for all headers. It basically repeat the top header everytime it reads a new page from the original report (this might not be a new page on excel though) - As below, it will reflect from Cape Town Centre to Cost Centre Code for every instance. I copied colA - C since the only data contained in A and B is header and cost centre info. Cape Town Centre INCOME STATEMENT PER COST CENTRE Cost Centre Code Cost Centre Name HO83 Cape Centre INCOME TOURISM NON TOURISM INCOME PROPERTY RIGHTS FUNDING OTHER INCOME The empty lines in the header contains the date, time and page numbers which are reflecting in column M. The rest of the empty lines are formatting. "Jacob Skaria" wrote: "7 header lines" is this only for the 1st header or for all headers...Post back with the first twenty lines of your data. (Just colA and B will do..) If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: I am getting a runtime error on line 13 - type mismatch: If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then "Jacob Skaria" wrote: If you mean to say all headers are in 7 lines; Adjust the below 3 lines in the code for 7 header lines varTemp = Rows(lngRow - 6 & ":" & lngLastRow) Rows(lngRow - 6 & ":" & lngLastRow).ClearContents Rows(1 & ":" & lngLastRow - lngRow + 8) = varTemp I have modified the earlier code a bit... Sub Macro() Dim lngRow As Long, lngLastRow As Long, varTemp As Variant Dim intCount As Integer lngRow = 1 lngLastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row Do While lngRow <= lngLastRow If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then intCount = intCount + 1 If intCount = 2 Then varTemp = Rows(lngRow - 1 & ":" & lngLastRow) Rows(lngRow - 1 & ":" & lngLastRow).ClearContents Sheets.Add After:=ActiveSheet Rows(1 & ":" & lngLastRow - lngRow + 2) = varTemp lngRow = 1 intCount = 0 lngLastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row End If End If lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: Thanks Jacob, It almost worked. The report I tested it on consisted of 3 cost centres. It split the first 2 perfectly and it pulled the header of the 3rd into a new sheet, but it left the transactional data on the original sheet. Also, I should probably have mentioned that the header line consists of the first 7 lines in the report. "Jacob Skaria" wrote: Take a backup of your actual data and test the below macro with your data in Sheet1.The macro works on the active sheet..I assume your first cost centre is in Row2 (just below the header row). Sub Macro() Dim lngRow As Long, lngLastRow As Long, varTemp As Variant lngRow = 3 lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Do While lngRow <= lngLastRow If Range("A" & lngRow) Like "[A-Z][A-Z]##" Then varTemp = Rows(lngRow - 1 & ":" & lngLastRow) Rows(lngRow - 1 & ":" & lngLastRow).ClearContents Sheets.Add After:=ActiveSheet Rows(1 & ":" & lngLastRow - lngRow + 2) = varTemp lngRow = 3 lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row End If lngRow = lngRow + 1 Loop End Sub If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: Jacob, To answer your queries: 1. When the report pulls from Dynamics it adds a header in excel for every page that existed in the Dynamics report. This results in multiple headings that inbedded inbetween the data. So in instances where the report spans 5 pages there will be 5 headings eventhough I only have 2 cost centres. 2. The report spans to Column M. 3. The report pulls the formatting from Dynamics so I don't require any formatting. Except if it is possible to get rid of all the additional headers without the "manual delete" :-) Thanks for your response. "Jacob Skaria" wrote: Marica Few queries 1. If you have headers repeated for each cost centre, why dont you split by the header rather than 2alpha/2numeric entry in Column A.. OR do you forsee any issues 2. Report spans to which column..(not really needed but still) 3. Do you need the formatting Or just the data in the new sheets.. If this post helps click Yes --------------- Jacob Skaria "Marica" wrote: I need to split a worksheet into multiple worksheets. The report pulled from Dynamics creates one excel worksheet for multiple cost centres. I need to split the report so that every cost centre is on a different worksheet. The reports have different lenghts so I cannot make use of a "every 100th line" approach. The common denominator is that when a new report starts column A has a value with 2 alphabetical characters and 2 numerical characters. Can I somehow make use of this to indicate to excel to split the report when it encounters a value with 2 alphabetical characters and 2 numerical characters in Column A? Example: REPORT HEADING HO13 data data data etc REPORT HEADING LB17 data data etc The result I require is that HO13 will remain on the current worksheet and LB17 will move to a new worksheet (including his report heading). Please keep in mind that I might have more than 2 cost centres on the report (this will also vary with every report generated). If anybody could be of assistance I would greatly appreciate. Please provide the explanation for dummies. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Split a worksheet into multiple worksheets of the same Excel file | Excel Discussion (Misc queries) | |||
Split a worksheet into multiple worksheets of the same Excel file | Excel Programming | |||
How to split up one spreadsheet into multiple worksheets | Excel Discussion (Misc queries) | |||
split results to multiple worksheets | Excel Worksheet Functions | |||
Data from One Worksheet split into Multiple Worksheets | Excel Programming |