Creating a workbook(s) based upon a cells value <--HELP please

[Dan]

Hello all -

I have a simple spreadsheet and would like to automate my current
CUT>COPY>PASTE methods. I would like to: 1) create a workbook with
cells based with the same values, 2) name the "new" workbook the cell
value name.

My current spreadsheet looks like this:

NAME DATE ZONE PRICE
Peaches 5/12/2011 AA $1.00
Apples 5/12/2011 B $3.00
Grapes 5/11/2011 AA $2.49
Pears 5/11/2011 $2.00
Bananas 5/12/2011 DD $1.89

So, based on the ZONE column, I want to create a separate Excel
workbook with all similar values (ie AA, B, DD). I'd like it to copy
the header row (which is row 1) and all of the columns.

Is there a simple macro that can do this request; I've scoured the
internet but get confused by the different variations of information I
see.

Thanks!

 

[Clif McIrvin]

Hello all -

I have a simple spreadsheet and would like to automate my current
CUT>COPY>PASTE methods. I would like to: 1) create a workbook with
cells based with the same values, 2) name the "new" workbook the cell
value name.

My current spreadsheet looks like this:

NAME DATE ZONE PRICE
Peaches 5/12/2011 AA $1.00
Apples 5/12/2011 B $3.00
Grapes 5/11/2011 AA $2.49
Pears 5/11/2011 $2.00
Bananas 5/12/2011 DD $1.89

So, based on the ZONE column, I want to create a separate Excel
workbook with all similar values (ie AA, B, DD). I'd like it to copy
the header row (which is row 1) and all of the columns.

Is there a simple macro that can do this request; I've scoured the
internet but get confused by the different variations of information I
see.

Thanks!

Hmmm ... is your "new workbook" in fact a new workBOOK, or only a new
workSHEET in the same workbook?

In either case, I'd suggest:

1. Open your current worksheet
2. Start the macro recorder (how depends on which version of Excel you
have)
3. Manually do all the steps involved in your current CUT>COPY>PASTE
methods
4. Stop recording
5. Examine the code generated by the macro recorder and modify as needed
to craft a macro that meets your needs.

I didn't try to give any detailed instructions ... it's too difficult to
guess what you already do or do not know.

If this makes sense to you, dive right in, and come back with your next
question!


Having said that; I re-read your post and wonder if simply using Excel's
built-in autofilter would give you what you are after --- instead of
creating a new worksheet for each distinct value in the ZONE column, you
have a command button in the ZONE header that allows you to select which
rows of data you can see.

In xl2010 (2003 also, if I remember correctly) you can right-click any
cell in your data region and create an autofilter from the context menu.

 

[Dan]

Hmmm ... is your "new workbook" in fact a new workBOOK, or only a new
workSHEET in the same workbook?

In either case, I'd suggest:

1. Open your current worksheet
2. Start the macro recorder (how depends on which version of Excel you
have)
3. Manually do all the steps involved in your current CUT>COPY>PASTE
methods
4. Stop recording
5. Examine the code generated by the macro recorder and modify as needed
to craft a macro that meets your needs.

I didn't try to give any detailed instructions ... it's too difficult to
guess what you already do or do not know.

If this makes sense to you, dive right in, and come back with your next
question!

Having said that; I re-read your post and wonder if simply using Excel's
built-in autofilter would give you what you are after --- instead of
creating a new worksheet for each distinct value in the ZONE column, you
have a command button in the ZONE header that allows you to select which
rows of data you can see.

In xl2010 (2003 also, if I remember correctly) you can right-click any
cell in your data region and create an autofilter from the context menu.

Ahh thanks for catching my mix-up. I want new WORKSHEETS inside the
WORKBOOK...I had it backwards.

And yes, I've use the Auto-Filter before, but the new WORKSHEETS I
create then go out to another source to automatically feed some data
thru our systems.

~D

 

[Gord Dibben]

For code see Ron de Bruin's site.

http://www.rondebruin.nl/tips.htm

Scroll down to "Copy/Paste/Merge Examples" and browse through the listings.


Gord Dibben MS Excel MVP