A macro to copy content/sub-contents of outlines and groups in excel

K

khaledab_86

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
 
I

Ivyleaf

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
Click to expand...

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.
 
I

Ivyleaf

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 -
Click to expand...

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.
 
K

khaledab_86

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 -
Click to expand...





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
 
K

khaledab_86

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 -
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

conso macro 0
Run macro from active workbook whose name keeps changing 20
Macro slow when copy and pasting 1
copy content from Multiple file to one file 3
A better Macro for Copying Data 2
Macro Related to Paste Values 2
Runtime 1004 Error Help Request 1
Code not working as expected 2

Top