| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Drop Down List Then Creating Saved File (with formulas intact)
Hi Guys,
I've got a problem that I can't see an obviously easy answer too (although I'm sure you'll prove me wrong!). I have a large spreadsheet that I want to break down using the drop down list feature. I need each file to show the contents of the drop down list for that option only. This I can do manually which is ok (albeit it REAL slow), however, the problem is that when I copy and paste the selected items into a new worksheet the formulas are all converted to values. Does anyone have any idas of how I can get around this (or at least speed the process up)? Any help is greatly appreciated. Best Regards, CalumMurdo Kennedy GB-1-1563 www.taekwondo.freeserve.co.uk |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Drop Down List Then Creating Saved File (with formulasintact)
Instead of filtering, you could mark the records that meet the criteria,
sort by the column that contains the mark, and copy the results to a new sheet. This will keep the formulas intact. (You can do this manually, or For example, type your first criterion in cell M1. In the first blank column to the right of the table, enter a formula that checks each row's data against the criterion: =IF(D2=$M$1,TRUE,"") Copy the formula down, then sort this column in descending order. Copy the headings and marked rows (excluding the column with the TRUE), and paste into a new workbook. I've added a sample workbook to my site: http://www.contextures.com/excelfiles.html#Function under the subheading 'Extract Items with Formulas' It has a named range (NameList) that contains the criteria, and uses the following code to copy the data to a new workbook: '======================== Sub CopyToWorkbook() Dim c As Range Dim rng As Range Dim r As Long r = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row Application.DisplayAlerts = False For Each c In Range("NameList") Range("M1").Value = c.Value Range("J2").Formula = "=IF(D2=$M$1,TRUE,"""")" Range("J2").AutoFill Destination:=Range("J2:J" & r) Range("A1:J38").Sort Key1:=Range("J2"), Order1:=xlDescending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom Columns("J:J").Copy Columns("J:J").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Set rng = Columns("J:J").SpecialCells(xlCellTypeConstants, 4) rng.Offset(-1, -9).Resize(rng.Rows.Count + 1, 9).Copy Workbooks.Add ActiveSheet.Paste ActiveWorkbook.SaveAs Filename:="c:\" & c.Value & ".xls" ActiveWorkbook.Close Next c Columns("J:J").Clear Range("M1").Clear Application.DisplayAlerts = False End Sub '=========================== CalumMurdo Kennedy wrote: Hi Guys, I've got a problem that I can't see an obviously easy answer too (although I'm sure you'll prove me wrong!). I have a large spreadsheet that I want to break down using the drop down list feature. I need each file to show the contents of the drop down list for that option only. This I can do manually which is ok (albeit it REAL slow), however, the problem is that when I copy and paste the selected items into a new worksheet the formulas are all converted to values. Does anyone have any idas of how I can get around this (or at least speed the process up)? Any help is greatly appreciated. Best Regards, CalumMurdo Kennedy GB-1-1563 www.taekwondo.freeserve.co.uk -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Using Drop Down List Then Creating Saved File (with formulas intact)
Hi Debra,
That is so cool! Thanks very much for taking the time to do that. I'm now going to spend the rest of the day looking very happy Best Regards, CalumMurdo Kennedy p.s. For some reason your message wasn't picked up by Freeserve (my ISP) so I guess I'll have to stick to using the Microsoft forums directly. -----Original Message----- Instead of filtering, you could mark the records that meet the criteria, sort by the column that contains the mark, and copy the results to a new sheet. This will keep the formulas intact. (You can do this manually, or For example, type your first criterion in cell M1. In the first blank column to the right of the table, enter a formula that checks each row's data against the criterion: =IF(D2=$M$1,TRUE,"") Copy the formula down, then sort this column in descending order. Copy the headings and marked rows (excluding the column with the TRUE), and paste into a new workbook. I've added a sample workbook to my site: http://www.contextures.com/excelfiles.html#Function under the subheading 'Extract Items with Formulas' It has a named range (NameList) that contains the criteria, and uses the following code to copy the data to a new workbook: '======================== Sub CopyToWorkbook() Dim c As Range Dim rng As Range Dim r As Long r = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row Application.DisplayAlerts = False For Each c In Range("NameList") Range("M1").Value = c.Value Range("J2").Formula = "=IF(D2=$M$1,TRUE,"""")" Range("J2").AutoFill Destination:=Range("J2:J" & r) Range("A1:J38").Sort Key1:=Range("J2"), Order1:=xlDescending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom Columns("J:J").Copy Columns("J:J").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Set rng = Columns("J:J").SpecialCells (xlCellTypeConstants, 4) rng.Offset(-1, -9).Resize(rng.Rows.Count + 1, 9).Copy Workbooks.Add ActiveSheet.Paste ActiveWorkbook.SaveAs Filename:="c:\" & c.Value & ".xls" ActiveWorkbook.Close Next c Columns("J:J").Clear Range("M1").Clear Application.DisplayAlerts = False End Sub '=========================== CalumMurdo Kennedy wrote: Hi Guys, I've got a problem that I can't see an obviously easy answer too (although I'm sure you'll prove me wrong!). I have a large spreadsheet that I want to break down using the drop down list feature. I need each file to show the contents of the drop down list for that option only. This I can do manually which is ok (albeit it REAL slow), however, the problem is that when I copy and paste the selected items into a new worksheet the formulas are all converted to values. Does anyone have any idas of how I can get around this (or at least speed the process up)? Any help is greatly appreciated. Best Regards, CalumMurdo Kennedy GB-1-1563 www.taekwondo.freeserve.co.uk -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| creating nested formulas from drop down box | Excel Discussion (Misc queries) | |||
| Help Creating a Drop Down List from a List in another worksheet | Excel Discussion (Misc queries) | |||
| How do I copy worksheets with drop-down menus intact? | Excel Worksheet Functions | |||
| Creating a drop down list from another drop down | Excel Discussion (Misc queries) | |||
| Formulas not updating until file is saved | Excel Discussion (Misc queries) | |||
Linear Mode
