Keeping Validation References When Breaking out Spreadsheets Using

I am using a version of Ron Debruin’s macro that breakouts spreadsheets into 
separate spreadsheets using a filter on a selected column.

The issue that I am having is that I have a series of validation references 
located in the main sheet in hidden rows (rows 1-14 are hidden).  I need to 
be able to retain these references in all the newly created sheets and retain 
the fixed references.

How do I do this?

Thanks in advance.

Modified Ron Debruin Macro
Sub FPR_Breakout_Worksheets()
    Dim calcmode As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim WSNew As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Dim Lrow As Long
    Dim FieldNum As Integer

    'Name of the sheet with your data
    Set ws1 = ActiveSheet  '<<< Change

    'Set filter range : A1 is the top left cell of your filter range and
    'the header of the first column, D is the last column in the filter range
    Set Rng = ws1.Range("A14:AM" & Rows.Count)

    'Set Field number of the filter column
    'This example filters on the first field in the range(change the field 
if needed)
    'In this case the range starts in A so Field:=1 is column A, 2 = column 
B, ......
    FieldNum = 1

    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    ' Add a worksheet to copy the a unique list and add the CriteriaRange
    Set ws2 = Worksheets.Add

    With ws2
        'first we copy the Unique data from the filter field to ws2
        Rng.Columns(FieldNum).AdvancedFilter _
                Action:=xlFilterCopy, _
                CopyToRange:=.Range("A1"), Unique:=True

        'loop through the unique list in ws2 and filter/copy to a new sheet
        Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
        For Each Cell In .Range("A3:A" & Lrow)

            Set WSNew = Sheets.Add
            On Error Resume Next
            WSNew.Name = Cell.Value
            If Err.Number > 0 Then
                MsgBox "Change the name of : " & WSNew.Name & " manually"
            End If
            On Error GoTo 0

            'Firstly, remove the AutoFilter
            ws1.AutoFilterMode = False

            'Filter the range
            Rng.AutoFilter Field:=FieldNum, Criteria1:="=" & Cell.Value

            'Copy the visible data and use PasteSpecial to paste to the new 
            With WSNew.Range("A1")
                ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
                .PasteSpecial Paste:=8
                .PasteSpecial xlPasteAll
                .PasteSpecial xlPasteFormats
                Application.CutCopyMode = False
            End With

            'Close AutoFilter
            ws1.AutoFilterMode = False

        Next Cell

        'Delete the ws2 sheet
        On Error Resume Next
        Application.DisplayAlerts = False
        Application.DisplayAlerts = True
        On Error GoTo 0

    End With

    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With
End Sub

1/11/2010 8:58:02 PM
excel.programming 6508 articles. 2 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 40


Similar Artilces:

Keep black parts black??
In 2002 I used this feature for a quick duotone. Colorize the photo, leaving black parts black and it worked great. Now in 2003, this feature is grayed out in spot color mode. What's up?? Thanks! Greg ...

Column Reference to External Source As a Variable
Can anyone help me convert the column referenced in the formula below into a variable that the user can define? More specifically, I have several columns that I need to read from an external workbook (Short_Billy.xls). Each column to the right of column C represents an additional day out in a 14 day projection from today (whose data is held in column C). In cell I5 of my active workbook (Inventory.xls), I would like the user to be able to enter a value representing the number of days out they would like to see the projection for (0=today=Column C, 1=Tomorrow=Column D, etc.). In cell I6, I...

using parameters
I have a form which the user selects the BlockNo. The other information that is entered in the form is : 1) NoOfRecordedTrees - RT 2)NoOfSurroundingTrees - ST When the BlockNo is entered, a query runs which picks up fertiliser rates for this Block for all sectors within that block. With RT and ST - it should do a calculation such that it uses Rate/ sector * (RT+ST) to find how much fertiliser is needed for each sector in each block. I would like to add a column to the existing query showing FertiliserAmt using these parameters. How do I specify them within the query. Thanks for your great...

sending mail using an alias email address
We are using Exchange Server 2007 with Outlook 2007 clients. I have several email addresses listed under my mail account on the Exchange Server for which I "receive" email. However, the server will not allow me to "send" email using any of these email addresses - as it only allows me to send using the primary address for my email account. I get a message saying "You are not allowed to send this message ... on behalf of another sender without permission to do so." What settings do I need to change on the Exchange Server so that it allows Outlook 2007 ...

Force a page break in code
I need to force a page break in the Detail_OnFormat Event when a value changes. How do I do that? Thanks, Bill Bill wrote: > I need to force a page break in the Detail_OnFormat > Event when a value changes. How do I do that? > > Thanks, > Bill Insert a PageBreak control from the ToolBox bar at the desired location. Even though such a control is never "visible" other than in design view they do still have a Visible property. That property effectively turns on and off the PageBreak so you can minipulate that in your code. -- Rick Brandt, Microsoft Access MVP...

Validation Question
I am creating an Excel drop-down using validation. The source contains many duplicate entries, which is unavoidable due to unrelated considerations. When I specify the range that contains the data, it is a named range, I get all the duplicates in the drop-down. Example, the data I need in the drop-down is in C2:C30. I named the range "brokers". When I specify Validation, List; the source is =brokers Those values that are in duplicate, or triplicate are repeated. From this validated list, I am creating a dependent drop-down list, using the instructions I got from the Contextures we...

how can i set up my pop and smtp account using proxy server in ou.
"Abhishek" <> wrote in message You'll need instructions from your ISP. -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Stockholm Consulting Group/KSG Microsoft OneNote FAQ: **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! ...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

Keeping a table in a form editable and checking that fields are filled in before allowing a save
Firstly apologies if this is the incorrect forum but I was looking for a general word forum and could not find one. Please point me to one if one exists. I am trying to create a form where I want to specify what items need to be filled in. (Review minutes from design reviews). I want to make certain fields mandatory like the date, attendees and check list used and want to block saving of the document with a warning until they are filled in. Is there a way of doing this? Also as a part of the review actions are filled in to a table. depending on how many actions there are the table...

Re: Using alias address
Brian Tillman wrote: > Vince <vinresp*> wrote: > > >>We use an exchange server at work for e-mail. I have set up an alias >>that I use for receiving mail but I wanted to use the alias for >>sending all the mail as well. I was told that it cannot be done in >>exchange but I can use a POP server. > > > I don't believe that's always true. For example, I have several accounts in > Outlook where I work, all within the same profile. Only one of those > accounts, the Exchange account, uses my work address. The ot...

Can Outlook 2003 use MSN Messenger INSTEAD of Windows Messenger?
I didn't get an answer to this question last week so I'm re-posting... I recently purchased a new computer (Windows XP Home Edition w/SP2) and loaded up all of the available updates to the OS, Internet Explorer, etc. Next, I installed Office 2003 Professional. I disabled Messenger integration with Outlook 2003 as discussed in other posts here. Next I installed MSN Messenger 6.2 and it seemed to run properly as a stand-alone application. So I re-enabled Messenger integration on Outlook 2003. The next time I booted up and ran Outlook, the Messenger icon appeared in the taskbar...

Is it possible to generate non-technical schema validation errors?
With the 1.0 Framework, I've worked out using the XmlValidatingReader. Since I'm using the validation errors as feedback to the end user, I'm hoping to get away from techy messages such as "The '' element has an invalid value according to its data type. An error occurred at file:///c:/work/prodika/main/code/apps/schemavalidation/XMLFile1.xml(8, 25)." and go with a user friendly message of "Max Dependents must be between 0 and 10". I've scoured the newsgroups, MSDN and docs for creating custom valid...

how to automatically suppress space before after column break?
Having Spacing Before and After on some of the styles, I seem to be unable to have the space before at the beginning of a column automatically dismissed when applying a column break. I have tried a couple of options under compatibility, but to no avail. This in on Word 2003. The No HTML function + No Space Before after column break do not solve the problem. Can you help please? Tools | Options | Compatibility: Suppress Space Before after a hard page or column break. If this isn't working, then check to make sure you don't have an empty paragraph before the first text pa...

Page break preview & blue line
I using Excel 2003 In page break preview I have always do some thing wrong I'm just pull the blue line by using mouse all page was destroyed It there any tips to return the blue line to the Default pages size setting As far as I know the 'default' print area is all the data, and once you have changed the print area and saved the file you can not get it to go back to the previous setting. If you have problems with this or any other proceedure do try to remember to save your file before you do the awkward bit and then you can go back to the saved copy it it does not work. ...

Creating Contacts For Accounts Using...
Hopefully as Microsoft CRM matures, many small time saving features will be added. One that should be a priority is the ability to add a new contact for an existing account using the common account information, i.e. address, phone number, web address, etc. Retyping the same company address in each time is not very productive. Thank you, Ed Podowski ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, f...

Linking Basic Beancounting spreadsheets?
I am new to Excel (and this forum) & want to make my own General Ledger, Monthy Synopsis, Trial Balances, Financial Statements etc. for a new business. I am reading _Basic_Beancounting_ by T. James Cook and wish to use Excel efficiently. (Just learned how to group tabs to make multiple identical forms. Works great and saves a pile of time!) Now I want to take the debit & credit totals from each account column in the monthly synopsis sheet and link them (automatically post) them to the appropriate column in their individual running totals of each General Ledger Account. The problem...

Word 2008 keeps crashing
I recently purchased a Macbook Pro and installed Office 2008. Whenever I type in Word or copy/paste from another document, I either get an error that says "Insufficient memory" or the application freezes and will not respond. Is there something I can do or just have to wait for an update? > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3285228210_9363962 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit On 2/7/08 10:37 AM, in article ee8c846.-1@web...

exchange 5.5 will not keep new server name.
We have exchange 5.5 in enviroment where windows server 2003 is setup along with active directory and active direcitory intergrated zone which is replicated to other servers. Problem: I enter the Exchange Server Name followed by the mailbox name then I select the button [CHECK NAME] In the exchange server name field after entering the new exchange server name the old exchange server name is displayed instead. This only happens in office 2003 professional and not in office 2000 professional "old exchange server name" How did you go about changing the name of the server...

command for breaking link in excel is not seen.
i have copied files containing fromulas to another file. while opening the new file, each time, it is asking whetehr to update the data from parent file. I wish to break the link in second file, but could not find the command for that. while opening links, it shows only three commands, which odes not include command for ' breaking links'. Hi Suresh, If the links are no longer required, delete the link formulae or convert them to values. You may find Bill Manville's FINDLINK.XLA addin useful This is freely downloadable at: --- Regards, ...

Outlook first use
Hi, when I open Outlook in a workstation for the first time, Outlook open setup and ask user to create a new profile. I have a utility to create profile automaticaly and this setup is deleting existing profile. Is it possible to Outlook don�t ask to create a new profile for the first time? Tks. Alex ...

Calculating Correlation using arrays
I have a sheet full of data for many products in chronological order. Column A is Date of manufacture Column B is time of manufacture Column C is the product Column D is a measurement from the automatic control system Column E contains occasional manual measurements for calibratio checks Up till now I have split the data out by grade and checked calibratio using pivot tables and also checked slope and offsets. After gainin exposure to all kinds of clever functions via this board I now wonde whether it would not be possible to automate these checks in some way ie on a separate sheet I list t...

Scroll horizontaly with mouse, create same system used to scroll .
Hi, I think it would be great if mouses adopted a second scroll button, for horizontal scrolling, just like the vertical one .... Indeed, when you work with wide Excel spreadsheets, you can easily scroll down but to scroll from left to right or vice-versa, you have to use the scroll bar or arrows and it's annoying... So, am I a millionnaire yet??? Hi Frederic, > I think it would be great if mouses adopted a second scroll button, for > horizontal scrolling, just like the vertical one .... Indeed, when you work > with wide Excel spreadsheets, you can easily scroll down b...

can I use 11 x 14 paper in office documents?
Is it possible to use a larger sheet of paper when using publisher? Yes. First select the paper size in the Printer Setup. -- Don Vancouver, USA "Prairie Inn" <Prairie> wrote in message > Is it possible to use a larger sheet of paper when using publisher? If your printer can handle it, yes. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question "Prairie Inn" <Prairie> w...

outlook in sub-domain to set use root-domain question!!!
Dear Sir Please see below more details,(We are using special railway line between Head office in Taipei and branch office in Tao-Yuan) Head office in Taipei: domain) Dc server * 2(One of it is GC Server), Front-End Exchange 2003 *1, Back-End Exchange 2003 * 2(One is named mail1, another is named mail2 ) Branch office in Tao-Yuan: Dc Server *1(No GC Server,No Exchange Server) After using ADMT v3 Tool, when I transfer an account from root named to After I ins...

Online Restore using NT Backup has no edb.chk or edb.log files
I have a single site with four servers running Exchange 5.5 SP4 on NT4 SP6a. I am using an internal 35/70 Compaq DLT. When I back up two servers at the same time using online method, I am missing the edb.log and and edb.chk files when trying to restore the db's. Is there a known issue for this? Thanks, Jim When you make online backup, you are backing up the database content perse, the logs files will be skipped because ntbackup cannot back up open files. I recomend you to adquire a third party backup software with open files and exchange database options, like Veritas to ensu...