Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a workbook(s) based upon a cells value <--HELP please
Hello all -
I have a simple spreadsheet and would like to automate my current CUTCOPYPASTE 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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a workbook(s) based upon a cells value <--HELP please
"Dan" wrote in message
... Hello all - I have a simple spreadsheet and would like to automate my current CUTCOPYPASTE 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 CUTCOPYPASTE 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. -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a workbook(s) based upon a cells value <--HELP please
On May 12, 5:13*pm, "Clif McIrvin" wrote:
"Dan" wrote in message ... Hello all - I have a simple spreadsheet and would like to automate my current CUTCOPYPASTE 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 CUTCOPYPASTE 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. -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a workbook(s) based upon a cells value <--HELP please
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 On Thu, 12 May 2011 17:35:22 -0700 (PDT), Dan wrote: On May 12, 5:13*pm, "Clif McIrvin" wrote: "Dan" wrote in message ... Hello all - I have a simple spreadsheet and would like to automate my current CUTCOPYPASTE 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 CUTCOPYPASTE 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. -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems creating a result based on 2 cells being true.. | Excel Worksheet Functions | |||
Copy cells based on conditions in one workbook to another workbook | Excel Discussion (Misc queries) | |||
Copy cells based on conditions in one workbook to another workbook | Excel Worksheet Functions | |||
Copy cells based on conditions in one workbook to another workbook | Excel Programming | |||
Extracting content based on criteria and creating new workbook | Excel Programming |