Formula to duplicate data to second sheet

[Donald Macleod]

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

 

[Sandy Mann]

Donald,

I would suggest that your idea of Autofiltering the data and copying to the
appropriate sheets is the way to go. You can record a Macro to do it for the
users - this would remove the possibility of user errors.

Post back if you need further help with the Macro.
--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Richard Buttrey]

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
__________________________

 

[bill k]

Using the macro recorder, gives me the following code.

Sub Macro2()

Range("A1100").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 th
result on the four sheets after selecting A1 on those sheets.
you may have to change the ranges etc but basically this macro will d
the job.

you could put this routine onto a button and / or into the workboo
code as a before print and or before save routine.

cancel any preliminary visits to farms............( :>

 

[Richard]

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

 

[Richard]

Sorry,

That third line in the loop should have read

Worksheets(x).Range("a1").CurrentRegion.ClearContents


Rgds

 

[Donald Macleod]

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