grouping

[MAMIBUSSOL]

I have 3 spreadsheets called apr11, may11, jun11 each spreadsheet has a fixed length ie the same number of columns and rows, I would like to group all 3 into 1 sheet using a script, any ideas on how to accomplish this

 

[Peter_SSs]

Try this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> GroupedData()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Sheets.Add(Before:=Sheets(1)).Name = "June Quarter"<br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Worksheets<br>            <SPAN style="color:#00007F">If</SPAN> ws.Name <> .Name <SPAN style="color:#00007F">Then</SPAN><br>                ws.UsedRange.Copy Destination:= _<br>                    .Range("A" & .Rows.Count).End(xlUp).Offset(1)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> ws<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>End <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>

If this is not what you want, please provide more detail.

 

[jim may]

Do you wish to have your Master sheet the same dimension as your other 3 sheets?
Should the Master Sheet SUM in cell C4 (for example)
apr11-Cell C4+may11-Cell C4+jun11-Cell C4
???

 

[MAMIBUSSOL]

Thanks for responding,
APR11
MAY11
JUN11

has 129 rows and 47 columns

APR11 will start at A1
MAY11 will start at A130 etc

I have come up with a possible solution but I have hit 2 snags
1. transaction (which is the combination of my 3 sheets
2. the names of the sheets might change

I need to cater for this eventuality

 

[Peter_SSs]

So, did you try my code? If so, remember ...

If this is not what you want, please provide more detail.

 

[MAMIBUSSOL]

Peter your code is right on the money, however I am getting all my sheets listed 1 at a time, is it possible to modify so I get sheets 4-6 only

 

[Peter_SSs]

Your first post sounded to me like there were only 3 sheets.

Doing sheets 4 - 6 is risky. If sheets are added/removed/rearranged, the code is likely to give you bad results. Can you fill out in a bit more detail just what you have and what you are trying to achieve?

Does the sheet this data is going on to already exist?
- If so, what is it called? Should any data already in it be removed?
- If not, what should it be called? Where should it be placed?

Will you be grouping data from other groups of sheets too?
- If so, how would we know which ones to get data from? Would the results be going onto the same sheet as the currect (Apr - Jun) data we are trying to move?

 

[MAMIBUSSOL]

I see what you mean.

the workbook is made up of the following

VAT VAT
Sales1 APR11
Sales2 MAY11
Sales3 JUN11
Purchase1 PURCHASE APR 11
Purchase2 PURCHASE MAY 11
Purchase3 PURCHASE JUN 11
PROFIT PROFIT

Purchase1, Purchase2, Purchase3 will always appear as above however Purchase 1 might be called PURCHASE APR 11 etc

I would like Purchase1, Purchase2, Purchase3 to go into a sheet after PROFIT called Creditors. When the code is run I will need to check whether Creditors exist, if does it needs to be deleted and recreated. the only reason to run the code is if a modification has been made to Purchase1, Purchase2 or Purchase3 at which time Creditors is out of date anyhow.

I hope this helps

 

[MAMIBUSSOL]

I have a worksheet and I want to remove any rows with no data in column AU

 

[MAMIBUSSOL]

Thanks Peter I have found a work around in regard to grabbing just the sheets I want, thanks for you time and patience.

just one thing I need to do to make this work.

how do I check whether the creditors sheet exists, and if it does delete it?