Export selections into new Workbooks

[gaba]

Hi All,
I'm trying to create a macro to do the following:
I have a Sheet with result groups, say they start on cell E:50. Each group
is separated by a column.

Each result group looks like this

3964-1.1
Chromium < 5 mg/L 1 0.005 EPA 200.8 10/15/2004
Cobalt < 5 mg/L 1 0.005 EPA 200.8 10/15/2004
Nickel < 5 mg/L 1 0.005 EPA 200.8 10/15/2004
Cadmium < 2 mg/L 1 0.002 EPA 200.8 10/15/2004
Tin < 5 mg/L 1 0.005 EPA 200.8 10/15/2004
Lead < 3 mg/L 1 0.003 EPA 200.8 10/15/2004

It can be one or more results. I need to create a Copy of the workbook with
each result group (always 7 columns, number of rows varies)disregard the
others and save it in the same folder with the First cell value (in this case
3964-1.1) .

It is possible? Any help would be greatly appreciated

 

[David]

Hi Gaba. I am a little unclear what you have and what you are looking for in
the end. First a line looks like this with an X between data elements?:

Chromium X < 5 X mg/L X 1 X 0.005 X EPA 200.8 X 10/15/2004

This line and maybe several other lines will have a "Group' header, some
thing like:
3964-1.1

You need these lines put in a separate workbook or worksheet? Maybe a
worsheet for each group, but in their own workbook? They already exist on an
Excel worksheet with each "group" separated by a column. This starts in cell
E50, so the next group starts in cell M50? How many groups do you have? If
each group had its own worksheet in this same book would that be sufficient?
Maybe each sheet shuld be named with the name of the group?

Thanks,
David

 

[David]

This starts in cell E50, it assumes that the groups are the only data on Row
50. It will copy the data for each group and put it on a new sheet in cell
A1. It will stop when it runs out of groups by reaching the right right side
of the "Data" worksheet, which is address IV50. Hope this will help. If the
new shets need to be in another workbook, then maybe the thing to do is
simply move them, after they have been created.

Sub Macro2()
ThisSheet = ActiveSheet.Name
Do Until ActiveCell.Address = ("$IV$50")
Selection.End(xlDown).Select
BottomOfData = ActiveCell.Row
Selection.End(xlUp).Select
Group = ActiveCell.Value
Sheets.Add
ActiveSheet.Name = Group
Sheets(ThisSheet).Select
ActiveCell.Range("A1:G" & (BottomOfData - 49)).Select
Selection.Copy
Sheets(Group).Select
ActiveSheet.Paste
Sheets(ThisSheet).Select
Application.CutCopyMode = False
Selection.End(xlToRight).Select
Loop
End Sub

 

[gaba]

Thanks so much David for your answer. I'll try your code.

This is what I'm trying to do: This workbook has three or five sheets (These
sheets are for importing data and calculations.) The last Sheet I'm working
on "Results" does the final calculations (different formulas to different
elements, check limits, etc) Then I'm grouping the results of these
calculations Starting on Cell E50. For each group I need to keep the raw data
and all calculations.

What I need to do is to copy the whole workbook (as a new one and rename?)
with only 1 group of results at the time. I was thinking on Saving the whole
file with a new name and just copying the "selected cells". Then I need to
save the files to the different folders (base on the first cell value).

I'll try to integrate your code with the rest.... I'll let you know how it
goes. I'll try to divide and conquer this problem...

Thanks so much
Gaba

 

[David]

Hi Gaba,
Like I said this assumes you are starting in cell E50 and the reason it
starts there has more to do with the loop, because the loop stops when you
get to the address "$IV$50". This copies what is in the cells, including
formulas, so if the formulas reference cells in the workbook, if you move the
sheets out to another workbook, you will have links to the original workbook.
Is this what you want?

I had assumed the values in the cells did not have formulas, but were
literal values. It is easy to do a "File Save As" and have a copy of the
original file, but the macro does not change the original worksheets, so no
data has been lost or even disturbed in any way. It is difficult to know
exactly what your summary sheet is trying to do with out seeing the sheet.
You can run this macro on as many sheets as you like, 3 to 5, as long as the
data begins on row 50 and it will create sheets for each group. Excel will
not allow you to name more than one sheet the same name in the same workbook,
so all groups must have a differant designation.
Thanks,
David