| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 - Split and re-consolidate master workbook
Hi
I have a list of customer names and addresses that are grouped by Sales Rep code. I need a way to split this list out into invidual worbooks to distribute to the appropriate rep, then reconsolidate the changes back in to one master again. After struggling all day, I've concluded that I do not have the knowledge to do this, can anyone help? Thanks in advance |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2007 - Split and re-consolidate master workbook
Hi Lisa
See the workbook example on this page http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Lisa" wrote in message ... Hi I have a list of customer names and addresses that are grouped by Sales Rep code. I need a way to split this list out into invidual worbooks to distribute to the appropriate rep, then reconsolidate the changes back in to one master again. After struggling all day, I've concluded that I do not have the knowledge to do this, can anyone help? Thanks in advance |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2007 - Split and re-consolidate master workbook
Hi, thanks Ron, I'm not really a VB programmer, but will give it a go. In
your example, how would I put multiple criteria in this line... rng.AutoFilter Field:=1, Criteria1:="=Netherlands" "Ron de Bruin" wrote: Hi Lisa See the workbook example on this page http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Lisa" wrote in message ... Hi I have a list of customer names and addresses that are grouped by Sales Rep code. I need a way to split this list out into invidual worbooks to distribute to the appropriate rep, then reconsolidate the changes back in to one master again. After struggling all day, I've concluded that I do not have the knowledge to do this, can anyone help? Thanks in advance |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2007 - Split and re-consolidate master workbook
Hi Lisa
In one column there is a max of 2 See the example code below this macro http://www.rondebruin.nl/copy5.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Lisa" wrote in message ... Hi, thanks Ron, I'm not really a VB programmer, but will give it a go. In your example, how would I put multiple criteria in this line... rng.AutoFilter Field:=1, Criteria1:="=Netherlands" "Ron de Bruin" wrote: Hi Lisa See the workbook example on this page http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Lisa" wrote in message ... Hi I have a list of customer names and addresses that are grouped by Sales Rep code. I need a way to split this list out into invidual worbooks to distribute to the appropriate rep, then reconsolidate the changes back in to one master again. After struggling all day, I've concluded that I do not have the knowledge to do this, can anyone help? Thanks in advance |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2007 - Split and re-consolidate master workbook
Hi
Rons code for extracting the worksheets into new workbooks is absolutely perfect for what I was looking for - many many thanks Ron. It has occured to me now however, that I need to set certain protection within the new workbooks as I create them (ie I'll need to protect certain cells, hide toolbars etc) I wondered whether there is a way I could therefore create blank workbooks with this protection in, then instead of creating new files through this macro, actually update the pre-made workbooks instead. I have tried setting the protection within the worksheets before I ran the macro, but this wasnt transferred to the new workbooks "Ron de Bruin" wrote: Hi Lisa In one column there is a max of 2 See the example code below this macro http://www.rondebruin.nl/copy5.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Lisa" wrote in message ... Hi, thanks Ron, I'm not really a VB programmer, but will give it a go. In your example, how would I put multiple criteria in this line... rng.AutoFilter Field:=1, Criteria1:="=Netherlands" "Ron de Bruin" wrote: Hi Lisa See the workbook example on this page http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Lisa" wrote in message ... Hi I have a list of customer names and addresses that are grouped by Sales Rep code. I need a way to split this list out into invidual worbooks to distribute to the appropriate rep, then reconsolidate the changes back in to one master again. After struggling all day, I've concluded that I do not have the knowledge to do this, can anyone help? Thanks in advance |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2007 - Split and re-consolidate master workbook
Hi Lisa
You can do it all in the code Before the Save line you can do what you want WSNew = The sheet in the new workbook For example lock a range and protect the worksheet before you save the file WSNew.Range("A1:A10").Locked = True WSNew.Protect "password" -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Lisa" wrote in message ... Hi Rons code for extracting the worksheets into new workbooks is absolutely perfect for what I was looking for - many many thanks Ron. It has occured to me now however, that I need to set certain protection within the new workbooks as I create them (ie I'll need to protect certain cells, hide toolbars etc) I wondered whether there is a way I could therefore create blank workbooks with this protection in, then instead of creating new files through this macro, actually update the pre-made workbooks instead. I have tried setting the protection within the worksheets before I ran the macro, but this wasnt transferred to the new workbooks "Ron de Bruin" wrote: Hi Lisa In one column there is a max of 2 See the example code below this macro http://www.rondebruin.nl/copy5.htm#AutoFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Lisa" wrote in message ... Hi, thanks Ron, I'm not really a VB programmer, but will give it a go. In your example, how would I put multiple criteria in this line... rng.AutoFilter Field:=1, Criteria1:="=Netherlands" "Ron de Bruin" wrote: Hi Lisa See the workbook example on this page http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Lisa" wrote in message ... Hi I have a list of customer names and addresses that are grouped by Sales Rep code. I need a way to split this list out into invidual worbooks to distribute to the appropriate rep, then reconsolidate the changes back in to one master again. After struggling all day, I've concluded that I do not have the knowledge to do this, can anyone help? Thanks in advance |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Consolidate excel 2007 | Excel Worksheet Functions | |||
| consolidate many workbook of 2007 | Excel Discussion (Misc queries) | |||
| Consolidate a worksheet from different workbook into one master | Excel Programming | |||
| Master sheet to split sheets back to another master sheet | Excel Programming | |||
| How to split and consolidate sheets into new workbooks | Excel Programming | |||
Linear Mode
