Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula to duplicate data to second sheet
I had been coerced into 'keeping an eye' on a spreadsheet.
The workbook is laid out with 5 worksheets, 'Full' and 4 others which are basically sections of 'Full' called 'NA', 'NB', 'NC', and 'ND'. The sheets themselves are list of customers with 16 pieces of information in separate columns. Needless to say every entry in 'Full' has to be duplicated in the sectional sheet. I tried to explain about the possibility of dumping the sectional sheets and using simple filtering to show the sectional data straight from 'Full'. From the facial expressions this produced I wondered if I'd grown 3 extra heads. I've now been asked if typing an entry in 'Full' can cause the same entry to be added to the corresponding section sheet. This would actually suit me as it would cut down on errors and mismatches between the sheets. Given the inane questions I've already been asked I'm not inclined to start a discussion about copying and pasting and assumed it would be fairly easy to do this with a conditional formula. I'm now having difficulty achieving this. If a formula is the solution then it merely has to act on the presence of NA, NB, NC or ND in column 7, (it could obviously be any column), and input all the data from that row to the next available row in the sectional sheet. Has anyone any helpful hints, preferably before I put in my notice and check into the funny farm,. which really could be any day now. I should say I'm not an advanced user, (how'd you guess), but I could handle further research if pointed in the right direction. Thanks for any help Donald Macleod |
#3
|
|||
|
|||
On Mon, 05 Sep 2005 21:00:39 GMT, "Donald Macleod"
wrote: I had been coerced into 'keeping an eye' on a spreadsheet. The workbook is laid out with 5 worksheets, 'Full' and 4 others which are basically sections of 'Full' called 'NA', 'NB', 'NC', and 'ND'. The sheets themselves are list of customers with 16 pieces of information in separate columns. Needless to say every entry in 'Full' has to be duplicated in the sectional sheet. I tried to explain about the possibility of dumping the sectional sheets and using simple filtering to show the sectional data straight from 'Full'. From the facial expressions this produced I wondered if I'd grown 3 extra heads. I've now been asked if typing an entry in 'Full' can cause the same entry to be added to the corresponding section sheet. This would actually suit me as it would cut down on errors and mismatches between the sheets. Given the inane questions I've already been asked I'm not inclined to start a discussion about copying and pasting and assumed it would be fairly easy to do this with a conditional formula. I'm now having difficulty achieving this. If a formula is the solution then it merely has to act on the presence of NA, NB, NC or ND in column 7, (it could obviously be any column), and input all the data from that row to the next available row in the sectional sheet. Has anyone any helpful hints, preferably before I put in my notice and check into the funny farm,. which really could be any day now. I should say I'm not an advanced user, (how'd you guess), but I could handle further research if pointed in the right direction. Thanks for any help Donald Macleod As you rightly identify, and as Punch didn't say to Judy, "That's not the way to do it". However if you really need to pursue this duplication solution, about the only way to do it is to use some VBA code. I don't know of any simple standard XL formula which could achieve it. The approach I'd adopt if the line of least resistance is your only option, is to add new records to the 'Full' sheet, then press an "Update" button which would run VBA code which would: a) First delete the contents of every NA, NB, NC & ND sheet b) Then re-populate each of the four sheets based on data in the 'Full' sheet If you feel this is an approach you wish to pursue, then please let me know. The code would be very simple and short, not more than a couple of dozen lines I'd guess, if that. Otherwise I'd check out that farm :-) HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#4
|
|||
|
|||
Using the macro recorder, gives me the following code. Sub Macro2() Range("A1:P100").Select Selection.AutoFilter Selection.AutoFilter Field:=7, Criteria1:="na" Selection.Copy Sheets("na").Select Range("A1").Select ActiveSheet.Paste Sheets("full").Select Application.CutCopyMode = False Selection.AutoFilter Field:=7, Criteria1:="nb" Selection.Copy Sheets("nb").Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Sheets("full").Select Selection.AutoFilter Field:=7, Criteria1:="nc" Selection.Copy Sheets("nc").Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Sheets("full").Select Selection.AutoFilter Field:=7, Criteria1:="nd" Selection.Copy Sheets("nd").Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Sheets("full").Select Selection.AutoFilter Range("A1").Select End Sub you'll see that it steps through the four autofilters and copies the result on the four sheets after selecting A1 on those sheets. you may have to change the ranges etc but basically this macro will do the job. you could put this routine onto a button and / or into the workbook code as a before print and or before save routine. cancel any preliminary visits to farms............( : ) -- bill k ------------------------------------------------------------------------ bill k's Profile: http://www.excelforum.com/member.php...nfo&userid=821 View this thread: http://www.excelforum.com/showthread...hreadid=402004 |
#5
|
|||
|
|||
Hi,
The following code will achieve what you want I think. It only caters for the 4 specific sectional sheets you mention. So if there was any chance of a new section, it would be a good idea to generalise the code further, and calculate the number of sectional sheets from a unique extract of Field 7. Go into VBA - ALT-F11; add a new Module, Insert Module; then add the procedure below to the Code Module you've just created. It assumes the Field Headings on the "Full" sheet are on row 1 starting in column A. You should therefore name A1 "DataTop" since I've used this name in the code. As I suggested in my earlier post, it loops through each sectional sheet, clearing the current data, filtering the Full sheet and copying the data into the sectional sheet. All you need to add is a suitable "Update" button on the Full sheet and assign the "FilterDataToSheets" procedure to the button. I was a bit out on my estimate - it's only 12 lines of code not 24 :-) Sub FilterDataToSheets() Dim rData As Range, x As Integer, stShName As String Set rData = Worksheets("Full").Range("DataTop").CurrentRegion For x = 1 To ActiveWorkbook.Worksheets.Count If Worksheets(x).Name < "Full" Then Worksheets(x).Range("a1").ClearContents stShName = Worksheets(x).Name rData.AutoFilter Field:=7, Criteria1:=stShName rData.SpecialCells(xlCellTypeVisible).Copy _ Destination:=Worksheets(x).Range("a1") End If rData.AutoFilter Next End Sub Regards |
#6
|
|||
|
|||
Sorry,
That third line in the loop should have read Worksheets(x).Range("a1").CurrentRegion.ClearConte nts Rgds |
#7
|
|||
|
|||
Richard, Sandy & Bill,
Absolutely incredible response. I actually looked last night, really only to make sure I had posted correctly, and found some answers already offered. I didn't have time to reply at that time and finished late, (again!!) tonight. Reference the advice given - If I can't solve it now, it isn't worth solving. Thanks for the very fast response and the quality of advice given. As my knowledge of Excel improves I hope I'm able to offer help to the less experienced. Thanks again Donald Macleod "Richard" wrote in message oups.com... Sorry, That third line in the loop should have read Worksheets(x).Range("a1").CurrentRegion.ClearConte nts Rgds |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Merger Two Data Sheet | Excel Discussion (Misc queries) | |||
Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions | |||
Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions |