Excel 2007 - Split and re-consolidate master workbook

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

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
 
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"
 
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
 
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"
 
Back
Top