Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro to copy content/sub-contents of outlines and groups in excel
Hellooo
Can someone please help ive been trying to do this for two months now and am debating whether this is even possible now? I have a spreadsheet that as lots of outlines and groups. What i want to be able to do is have a macro that finds a specfic group and then copy the content or sub tasks of that group. The macro needs to keep in mind that the location of the groups is not always in the same location within the spreadsheet and the volume of the content can vary in terms of size. further more the spreadsheet that it copies to needs too, the macro must make sure that content already in the spreadsheet is not overwritten, so should copy below onto the next line! Hope that made sense, Any ideas anyone??? Here is the code so far: Sub Copy() ' Open Data Dump workbook - the workbook to be copied too Workbooks.Open Filename:="C:\Documents and Settings\Desktop \Tester \Tester.xls" Windows("Summary.xls").Activate ' Check if the first cell contains data. If not then close file - will need a message box that tells me theres no data 'Sheet1.Activate If WorksheetFunction.CountA(Cells) = 0 Then ActiveWorkbook.Close SaveChanges:=False ActiveWorkbook.Saved = True 'If Range(" Sheet1!A1") = "" Then 'ActiveWorkbook.Close SaveChanges:=False 'ActiveWorkbook.Saved = True ' If the cell does contain data then transfer the row of data across to the Summary file Else ' the ranges are abigious does not necessarily mean this will be the distinct ranges at all time. I need a condition that allows a finding of the specific subtask and then select its ranges Range("A1:J300").Select Selection.Copy Windows("Tester.xls").Activate Sheets("Sheet1").Activate Range("B7").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True 'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ ':=False, Transpose:=True ' Run the trade entry and formatting macros in Trade Data file 'Enter_Trade End If ' Save and close the Trade Data file 'ActiveWorkbook.Close SaveChanges:=True 'ActiveWorkbook.Saved = True End Sub Thanks sooo much youve no idea how much i will appreciate it if someone can help Kay |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro to copy content/sub-contents of outlines and groups inexcel
On Apr 5, 1:38*am, wrote:
Hellooo Can someone please help ive been trying to do this for two months now and am debating whether this is even possible now? I have a spreadsheet that as lots of outlines and groups. What i want to be able to do is have a macro that finds a specfic group and then copy the content or sub tasks of that group. The macro needs to keep in mind that the location of the groups is not always in the same location within the spreadsheet and the volume of the content can vary in terms of size. further more the spreadsheet that it copies to needs too, the macro must make sure that content already in the spreadsheet is not overwritten, so should copy below onto the next line! Hope that made sense, Any ideas anyone??? Here is the code so far: Sub Copy() * * *' * Open Data Dump workbook - the workbook to be copied too * * Workbooks.Open Filename:="C:\Documents and Settings\Desktop \Tester \Tester.xls" * * Windows("Summary.xls").Activate * * *' * Check if the first cell contains data. If not then close file - will need a message box that tells me theres no data * * 'Sheet1.Activate * * If WorksheetFunction.CountA(Cells) = 0 Then * * * * *ActiveWorkbook.Close SaveChanges:=False * * * * ActiveWorkbook.Saved = True * * * * * * 'If Range(" Sheet1!A1") = "" Then * * * * * * 'ActiveWorkbook.Close SaveChanges:=False * * * * * * 'ActiveWorkbook.Saved = True * * * * *' * If the cell does contain data then transfer the row of data across to the Summary file * * Else ' the ranges are abigious does not necessarily mean this will be the distinct ranges at all time. I need a condition that allows a finding of the specific subtask and then select its ranges * * * * Range("A1:J300").Select * * * * Selection.Copy * * * * Windows("Tester.xls").Activate * * * * Sheets("Sheet1").Activate * * * * Range("B7").Select * * * * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True * * * * 'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ * * * * ':=False, Transpose:=True * * * * *' * Run the *trade entry and *formatting macros in Trade Data file * * * * 'Enter_Trade * * End If * * *' * Save and close the Trade Data file * * 'ActiveWorkbook.Close SaveChanges:=True * * 'ActiveWorkbook.Saved = True End Sub Thanks sooo much youve no idea how much i will appreciate it if someone can help Kay Hi Kay, Need a bit more info I'm afraid (well I do anyway). You say you need to find a 'specific group' and that the sheet contains lots of outlines and groups. Do you mean as in the rows are grouped by using the 'Group and Outline' feature in the 'Data' menu, or just that the data is visually grouped with blank space in between and cunks of data here and there? In your actual macro comments, you say in the process "if ccell does contain data then transfer row across to Summary file" which doesn't make any mention of the grouping part of your question, which is why I am unsure. You say you need to find a 'Subtask', but haven't alluded as to what classifies a 'Subtask'. In a macro you can look for pretty much anything, even if you have to loop through every single cell to find the pattern, but you have to have a pattern that you are searching for. From what you have described, I am sure I or someone else can help, but personally I will need a clearer picture in my mind of what you are trying to do. Cheers, Ivan. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro to copy content/sub-contents of outlines and groups inexcel
On Apr 5, 1:54*am, Ivyleaf wrote:
On Apr 5, 1:38*am, wrote: Hellooo Can someone please help ive been trying to do this for two months now and am debating whether this is even possible now? I have a spreadsheet that as lots of outlines and groups. What i want to be able to do is have a macro that finds a specfic group and then copy the content or sub tasks of that group. The macro needs to keep in mind that the location of the groups is not always in the same location within the spreadsheet and the volume of the content can vary in terms of size. further more the spreadsheet that it copies to needs too, the macro must make sure that content already in the spreadsheet is not overwritten, so should copy below onto the next line! Hope that made sense, Any ideas anyone??? Here is the code so far: Sub Copy() * * *' * Open Data Dump workbook - the workbook to be copied too * * Workbooks.Open Filename:="C:\Documents and Settings\Desktop \Tester \Tester.xls" * * Windows("Summary.xls").Activate * * *' * Check if the first cell contains data. If not then close file - will need a message box that tells me theres no data * * 'Sheet1.Activate * * If WorksheetFunction.CountA(Cells) = 0 Then * * * * *ActiveWorkbook.Close SaveChanges:=False * * * * ActiveWorkbook.Saved = True * * * * * * 'If Range(" Sheet1!A1") = "" Then * * * * * * 'ActiveWorkbook.Close SaveChanges:=False * * * * * * 'ActiveWorkbook.Saved = True * * * * *' * If the cell does contain data then transfer the row of data across to the Summary file * * Else ' the ranges are abigious does not necessarily mean this will be the distinct ranges at all time. I need a condition that allows a finding of the specific subtask and then select its ranges * * * * Range("A1:J300").Select * * * * Selection.Copy * * * * Windows("Tester.xls").Activate * * * * Sheets("Sheet1").Activate * * * * Range("B7").Select * * * * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True * * * * 'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ * * * * ':=False, Transpose:=True * * * * *' * Run the *trade entry and *formatting macros in Trade Data file * * * * 'Enter_Trade * * End If * * *' * Save and close the Trade Data file * * 'ActiveWorkbook.Close SaveChanges:=True * * 'ActiveWorkbook.Saved = True End Sub Thanks sooo much youve no idea how much i will appreciate it if someone can help Kay Hi Kay, Need a bit more info I'm afraid (well I do anyway). You say you need to find a 'specific group' and that the sheet contains lots of outlines and groups. Do you mean as in the rows are grouped by using the 'Group and Outline' feature in the 'Data' menu, or just that the data is visually grouped with blank space in between and cunks of data here and there? In your actual macro comments, you say in the process "if ccell does contain data then transfer row across to Summary file" which doesn't make any mention of the grouping part of your question, which is why I am unsure. You say you need to find a 'Subtask', but haven't alluded as to what classifies a 'Subtask'. In a macro you can look for pretty much anything, even if you have to loop through every single cell to find the pattern, but you have to have a pattern that you are searching for. From what you have described, I am sure I or someone else can help, but personally I will need a clearer picture in my mind of what you are trying to do. Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi Again, This doesn't do what you are asking for (since I am still not 100% sure what that is), but I think it might simplify your existing code a bit and you might be able to use it to explain what else you need it to do. What I have assumed is: The code you are writing will be residin in your 'Summary book'. This made the most sense to me since otherwise you would need to add the code to each data book which would be impractical; either that or you could write an addin which could make sense, but I'm doing it this way for now :). You will start with your summary book open and with the target summary sheet active. What you want to do is open the source data book, check if the first cell is empty, if it is then copy the first row to the summary sheet at the next vacant row then close the source data book (no point saving since all you have done is copy from it). If ithe first cell is empty, then close the source book and display a message to let you know. - I know this isn't what you want, but until we establish that more clearly, this can hopefully demonstrate the concept. If this is close, then all we have to work out is how to find the blocks of data you want transferred and probably run a loop to get them all. If you are looking for actual grouped rows, then you can probably use the OutlineLevel property of the row to build a pucture of the data structure. Anyway, here is the reworked code: Sub Copy() Dim SrcFName As String, LastRow As Long Dim SrcBook As Workbook, SumBook As Workbook 'Set file name of Summary book SrcFName = "C:\Documents and Settings\Desktop\Tester.xls" 'Set SrcBook before opening Summary book Set SumBook = ThisWorkbook 'Open Data Dump workbook - the workbook to be copied from Application.ScreenUpdating = False Set SrcBook = Workbooks.Open(SrcFName) SumBook.Activate 'Check if first cell contains data. If not, close file and end If IsEmpty(SrcBook.Sheets(1).Cells(1)) Then SrcBook.Close False Application.ScreenUpdating = True MsgBox "Source book contained no data", _ vbInformation + vbOKOnly, "No Data" Exit Sub End If 'If the cell does contain data then transfer the row of 'data across to the Summary file 'Find next blank row of summary file LastRow = SumBook.ActiveSheet.Range("A65536") _ .End(xlUp).Row + 1 'Copy row to summary book SumBook.ActiveSheet.Rows(LastRow) = _ SrcBook.ActiveSheet.Rows(1).Value 'Run the trade entry and formatting macros 'Close the source workbook SrcBook.Close SaveChanges:=False Application.ScreenUpdating = True End Sub Cheers, Ivan. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro to copy content/sub-contents of outlines and groups inexcel
On Apr 4, 4:38*pm, Ivyleaf wrote:
On Apr 5, 1:54*am, Ivyleaf wrote: On Apr 5, 1:38*am, wrote: Hellooo Can someone please help ive been trying to do this for two months now and am debating whether this is even possible now? I have a spreadsheet that as lots of outlines and groups. What i want to be able to do is have a macro that finds a specfic group and then copy the content or sub tasks of that group. The macro needs to keep in mind that the location of the groups is not always in the same location within the spreadsheet and the volume of the content can vary in terms of size. further more the spreadsheet that it copies to needs too, the macro must make sure that content already in the spreadsheet is not overwritten, so should copy below onto the next line! Hope that made sense, Any ideas anyone??? Here is the code so far: Sub Copy() * * *' * Open Data Dump workbook - the workbook to be copied too * * Workbooks.Open Filename:="C:\Documents and Settings\Desktop \Tester \Tester.xls" * * Windows("Summary.xls").Activate * * *' * Check if the first cell contains data. If not then close file - will need a message box that tells me theres no data * * 'Sheet1.Activate * * If WorksheetFunction.CountA(Cells) = 0 Then * * * * *ActiveWorkbook.Close SaveChanges:=False * * * * ActiveWorkbook.Saved = True * * * * * * 'If Range(" Sheet1!A1") = "" Then * * * * * * 'ActiveWorkbook.Close SaveChanges:=False * * * * * * 'ActiveWorkbook.Saved = True * * * * *' * If the cell does contain data then transfer the row of data across to the Summary file * * Else ' the ranges are abigious does not necessarily mean this will be the distinct ranges at all time. I need a condition that allows a finding of the specific subtask and then select its ranges * * * * Range("A1:J300").Select * * * * Selection.Copy * * * * Windows("Tester.xls").Activate * * * * Sheets("Sheet1").Activate * * * * Range("B7").Select * * * * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True * * * * 'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ * * * * ':=False, Transpose:=True * * * * *' * Run the *trade entry and *formatting macros in Trade Data file * * * * 'Enter_Trade * * End If * * *' * Save and close the Trade Data file * * 'ActiveWorkbook.Close SaveChanges:=True * * 'ActiveWorkbook.Saved = True End Sub Thanks sooo much youve no idea how much i will appreciate it if someone can help Kay Hi Kay, Need a bit more info I'm afraid (well I do anyway). You say you need to find a 'specific group' and that the sheet contains lots of outlines and groups. Do you mean as in the rows are grouped by using the 'Group and Outline' feature in the 'Data' menu, or just that the data is visually grouped with blank space in between and cunks of data here and there? In your actual macro comments, you say in the process "if ccell does contain data then transfer row across to Summary file" which doesn't make any mention of the grouping part of your question, which is why I am unsure. You say you need to find a 'Subtask', but haven't alluded as to what classifies a 'Subtask'. In a macro you can look for pretty much anything, even if you have to loop through every single cell to find the pattern, but you have to have a pattern that you are searching for. From what you have described, I am sure I or someone else can help, but personally I will need a clearer picture in my mind of what you are trying to do. Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi Again, This doesn't do what you are asking for (since I am still not 100% sure what that is), but I think it might simplify your existing code a bit and you might be able to use it to explain what else you need it to do. What I have assumed is: The code you are writing will be residin in your 'Summary book'. This made the most sense to me since otherwise you would need to add the code to each data book which would be impractical; either that or you could write an addin which could make sense, but I'm doing it this way for now :). You will start with your summary book open and with the target summary sheet active. What you want to do is open the source data book, check if the first cell is empty, if it is then copy the first row to the summary sheet at the next vacant row then close the source data book (no point saving since all you have done is copy from it). If ithe first cell is empty, then close the source book and display a message to let you know. - I know this isn't what you want, but until we establish that more clearly, this can hopefully demonstrate the concept. If this is close, then all we have to work out is how to find the blocks of data you want transferred and probably run a loop to get them all. If you are looking for actual grouped rows, then you can probably use the OutlineLevel property of the row to build a pucture of the data structure. Anyway, here is the reworked code: Sub Copy() * * Dim SrcFName As String, LastRow As Long * * Dim SrcBook As Workbook, SumBook As Workbook * * 'Set file name of Summary book * * SrcFName = "C:\Documents and Settings\Desktop\Tester.xls" * * 'Set SrcBook before opening Summary book * * Set SumBook = ThisWorkbook * * 'Open Data Dump workbook - the workbook to be copied from * * Application.ScreenUpdating = False * * Set SrcBook = Workbooks.Open(SrcFName) * * SumBook.Activate * * 'Check if first cell contains data. If not, close file and end * * If IsEmpty(SrcBook.Sheets(1).Cells(1)) Then * * * * SrcBook.Close False * * * * Application.ScreenUpdating = True * * * * MsgBox "Source book contained no data", _ * * * * * * vbInformation + vbOKOnly, "No Data" * * * * Exit Sub * * End If * * 'If the cell does contain data then transfer the row of * * 'data across to the Summary file * * * * 'Find next blank row of summary file * * * * LastRow = SumBook.ActiveSheet.Range("A65536") _ * * * * * * .End(xlUp).Row + 1 * * * * 'Copy row to summary book * * * * SumBook.ActiveSheet.Rows(LastRow) = _ * * * * * * SrcBook.ActiveSheet.Rows(1).Value * * 'Run the trade entry and formatting macros * * 'Close the source workbook * * SrcBook.Close SaveChanges:=False * * Application.ScreenUpdating = True End Sub Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi Ivan Firstly my apologies for the late reply and thank you for replying back You are definately on the right track. Your copying process from one spreadsheet and its validation is what I am looking at to be achieved so all that is required is to effectively understand this outlining and grouping business. :-( ok, your first post suggested whether it was the group and outlining from the 'Data Menu' and that is exactly the case but with 4 levels based upon rows. The comment in the macro is what I have only been able to achieve in terms of coding. So as an example imagine this. Animals Colour Location Age (years) Birds Parrot Green South Africa 5 Buggie Blue England 2 Black Bird Black England 12 Fishes Sword Fish Gold Fish Now imagine over 5000 rows like this. So Animal, Colour, Location and Age are column headings Bird is the main group and Parrot, Buggie and Black Bird are the subtasks or sub content of the main group 'Bird' that I want to copy. So the macro will need to find Bird and then copy 'Bird and its content' to another spreadsheet (something i failed to mention earlier), that also has the same column headings. Where the above is only of 2 levels the spreadsheet i want to copy from is 4 levels. There will need to be validation in the macro to check the following: - That finding of a group will not always be in the same location in a spreadsheet so where Bird is in co ordinates B:10 one day another day may be G:2 - The sub-content(sub tasks) are not always of the same volume all the time so where Bird is showing 3 instances another day may be 20 instances - macro will need to be able to identify volume and understand to copy that volume. - Also the domain spreadsheet that it copies too - the macro will need to understand that it needs to copy the new group to the end of the work sheet not overwriting contents already in the spreadsheet. GOSH i hope this is making sense :-) This macro will need to be versatile in the sense that I may only be wanting this group for now on one particular spreadsheet but in the furture i may need to create another spreadsheet that looks at using the same principal but requires copying of another group. So i should be able to go into the macro and change the relevant details. Any more info you require please do ask :-) Thank you sooo much. Kay |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro to copy content/sub-contents of outlines and groups inexcel
On Apr 7, 4:03*pm, wrote:
On Apr 4, 4:38*pm, Ivyleaf wrote: On Apr 5, 1:54*am, Ivyleaf wrote: On Apr 5, 1:38*am, wrote: Hellooo Can someone please help ive been trying to do this for two months now and am debating whether this is even possible now? I have a spreadsheet that as lots of outlines and groups. What i want to be able to do is have a macro that finds a specfic group and then copy the content or sub tasks of that group. The macro needs to keep in mind that the location of the groups is not always in the same location within the spreadsheet and the volume of the content can vary in terms of size. further more the spreadsheet that it copies to needs too, the macro must make sure that content already in the spreadsheet is not overwritten, so should copy below onto the next line! Hope that made sense, Any ideas anyone??? Here is the code so far: Sub Copy() * * *' * Open Data Dump workbook - the workbook to be copied too * * Workbooks.Open Filename:="C:\Documents and Settings\Desktop \Tester \Tester.xls" * * Windows("Summary.xls").Activate * * *' * Check if the first cell contains data. If not then close file - will need a message box that tells me theres no data * * 'Sheet1.Activate * * If WorksheetFunction.CountA(Cells) = 0 Then * * * * *ActiveWorkbook.Close SaveChanges:=False * * * * ActiveWorkbook.Saved = True * * * * * * 'If Range(" Sheet1!A1") = "" Then * * * * * * 'ActiveWorkbook.Close SaveChanges:=False * * * * * * 'ActiveWorkbook.Saved = True * * * * *' * If the cell does contain data then transfer the row of data across to the Summary file * * Else ' the ranges are abigious does not necessarily mean this will be the distinct ranges at all time. I need a condition that allows a finding of the specific subtask and then select its ranges * * * * Range("A1:J300").Select * * * * Selection.Copy * * * * Windows("Tester.xls").Activate * * * * Sheets("Sheet1").Activate * * * * Range("B7").Select * * * * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True * * * * 'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ * * * * ':=False, Transpose:=True * * * * *' * Run the *trade entry and *formatting macros in Trade Data file * * * * 'Enter_Trade * * End If * * *' * Save and close the Trade Data file * * 'ActiveWorkbook.Close SaveChanges:=True * * 'ActiveWorkbook.Saved = True End Sub Thanks sooo much youve no idea how much i will appreciate it if someone can help Kay Hi Kay, Need a bit more info I'm afraid (well I do anyway). You say you need to find a 'specific group' and that the sheet contains lots of outlines and groups. Do you mean as in the rows are grouped by using the 'Group and Outline' feature in the 'Data' menu, or just that the data is visually grouped with blank space in between and cunks of data here and there? In your actual macro comments, you say in the process "if ccell does contain data then transfer row across to Summary file" which doesn't make any mention of the grouping part of your question, which is why I am unsure. You say you need to find a 'Subtask', but haven't alluded as to what classifies a 'Subtask'. In a macro you can look for pretty much anything, even if you have to loop through every single cell to find the pattern, but you have to have a pattern that you are searching for. From what you have described, I am sure I or someone else can help, but personally I will need a clearer picture in my mind of what you are trying to do. Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi Again, This doesn't do what you are asking for (since I am still not 100% sure what that is), but I think it might simplify your existing code a bit and you might be able to use it to explain what else you need it to do. What I have assumed is: The code you are writing will be residin in your 'Summary book'. This made the most sense to me since otherwise you would need to add the code to each data book which would be impractical; either that or you could write an addin which could make sense, but I'm doing it this way for now :). You will start with your summary book open and with the target summary sheet active. What you want to do is open the source data book, check if the first cell is empty, if it is then copy the first row to the summary sheet at the next vacant row then close the source data book (no point saving since all you have done is copy from it). If ithe first cell is empty, then close the source book and display a message to let you know. - I know this isn't what you want, but until we establish that more clearly, this can hopefully demonstrate the concept. If this is close, then all we have to work out is how to find the blocks of data you want transferred and probably run a loop to get them all. If you are looking for actual grouped rows, then you can probably use the OutlineLevel property of the row to build a pucture of the data structure. Anyway, here is the reworked code: Sub Copy() * * Dim SrcFName As String, LastRow As Long * * Dim SrcBook As Workbook, SumBook As Workbook * * 'Set file name of Summary book * * SrcFName = "C:\Documents and Settings\Desktop\Tester.xls" * * 'Set SrcBook before opening Summary book * * Set SumBook = ThisWorkbook * * 'Open Data Dump workbook - the workbook to be copied from * * Application.ScreenUpdating = False * * Set SrcBook = Workbooks.Open(SrcFName) * * SumBook.Activate * * 'Check if first cell contains data. If not, close file and end * * If IsEmpty(SrcBook.Sheets(1).Cells(1)) Then * * * * SrcBook.Close False * * * * Application.ScreenUpdating = True * * * * MsgBox "Source book contained no data", _ * * * * * * vbInformation + vbOKOnly, "No Data" * * * * Exit Sub * * End If * * 'If the cell does contain data then transfer the row of * * 'data across to the Summary file * * * * 'Find next blank row of summary file * * * * LastRow = SumBook.ActiveSheet.Range("A65536") _ * * * * * * .End(xlUp).Row + 1 * * * * 'Copy row to summary book * * * * SumBook.ActiveSheet.Rows(LastRow) = _ * * * * * * SrcBook.ActiveSheet.Rows(1).Value * * 'Run the trade entry and formatting macros * * 'Close the source workbook * * SrcBook.Close SaveChanges:=False * * Application.ScreenUpdating = True End Sub Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi Ivan Firstly my apologies for the late reply and thank you for replying back You are definately on the right track. Your copying process from one spreadsheet and its validation is what I am looking at to be achieved so all that is required is to effectively understand this outlining and grouping business. :-( ok, your first post suggested whether it was the group and outlining from the 'Data Menu' and that is exactly the case but with 4 levels based upon rows. The comment in the macro is what I have only been able to achieve in terms of coding. So as an example imagine this. Animals * * * * * * * * *Colour Location * * * * * * * * * * Age (years) Birds * * Parrot * * * * * * * * Green * * * * * * * * * * * South Africa * * * * * * *5 * * Buggie * * * * * * * *Blue England * * * * * * * * * *2 * * Black Bird * * * * * Black England * * * * * * * * * *12 Fishes * *Sword Fish * *Gold Fish Now imagine over 5000 rows like this. So Animal, Colour, Location and Age are column headings Bird is the main group and Parrot, Buggie and Black Bird are the subtasks or sub content of the main group 'Bird' that I want to copy. So the macro will need to find Bird and then copy 'Bird and its content' to another spreadsheet (something i failed to mention earlier), that also has the same column headings. Where the above is only of 2 levels the spreadsheet i want to copy from is 4 levels. There will need to be validation in the macro to check the following: * * - That finding of a group will not always be in the same location in a spreadsheet so where Bird is in co ordinates B:10 one day another day may be G:2 * * - The sub-content(sub tasks) are not always of the same volume all the time so where Bird is showing 3 instances another day may be 20 instances - macro will need to be able to identify volume and understand to copy that volume. * * - Also the domain spreadsheet that it copies too - the macro will need to understand that it needs to copy the new group to the end of the work sheet not overwriting contents already in the spreadsheet. * * * * * * * * * * * * * * * * * * * * * * * * * * *GOSH i hope this is making sense :-) This macro will need to be versatile in the sense that I may only be wanting this group for now on one particular spreadsheet but in the furture i may need to create another spreadsheet that looks at using the same principal but requires copying of another group. So i should be able to go into the macro and change the relevant details. Any more info you require please do ask :-) Thank you sooo much. Kay- Hide quoted text - - Show quoted text - Hi, Ive just has a look at the format of the example that i posted and its muddled up so it may help to write down on a paper or type into excel you have Animal, Colour, Location and Age as column headings next cell you have Bird next cell under Bird you have parrot celll next to this you have green and next cell next to green you have South Africa and next cell next to South Africa you have 5 ((THIS PROCESS FOLLOWS ONTO NEW ROW - buggie, blue, england, 2 AND NEXT ROW - Black bird, black, england, 12 Thanks again Kind Regards Kay |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pasting into Groups/Outlines | Excel Discussion (Misc queries) | |||
Copy Outlines in Excel 2007 | Excel Discussion (Misc queries) | |||
Excel:Swap cell contents-including cell groups-betwen 2 locations | Excel Discussion (Misc queries) | |||
Macro VBA code to copy content from one worksheet to another | Excel Programming | |||
Can I lock rows together and then filter for content in groups? | Excel Discussion (Misc queries) |