Sharing data across worksheets within a workbook based on identifi

[deedle93]

I'm trying to figure out how I can automatically have data inserted into one
worksheet copied to another based on an identifier. For example...In my
first worksheet (i'll call it "all data") I want to use the first column to
give each line item an identity (in this case either the letter "a" or the
letter "b"). Once I've input all of my line items I want to have a formula
that will automatically copy all the line item data for those lines
identified with an "a" to a separate worksheet (I'll call it "A"). I want to
be able to do the same for by "b" line items. In the end I should have three
worksheets. the first contains all by "a" and "b" items in aggregate. The
second is only the "a" items (as extracted by the formula using the "a"
identified) and the third worksheet is the "b" items (as extracted by the
formula using the "b" identifier). Can this been done? Could I reverse it
and somehow input the data into the "a" and "b" worksheets and then use a
command to aggregate them into the "all data" worksheet?

[cincode5]

Deedle93

This advice assumes you are somewhat familar with creating macros and VB.

Try writing a VB Macro for each identifier (A, B, etc) that you want to
create a unique list for. Make sure your 'All Data' Sheet has the
Auto-Filter ON for the identifier column. The seperate macro codes read as
follows:

Sub Identifier_A() 'and B, and C etc.

Selection.AutoFilter Field:=1, Criteria1:="a" 'and similarly for B, C,
D, etc
Columns("A:C").Copy 'these are the colums with data you want to copy
Sheets("SheetA").Select 'Change "SheetA" to reference each Identifier tab
Columns("A:C").Select 'same column references as above
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("Sheet1").Select 'This returns you to your All Data sheet
Application.CutCopyMode = False
Selection.AutoFilter Field:=1
Range("A1").Select
End Sub

You will initially need to create the tabs for each identifier. Make sure
to name them identically to the names referenced in the VB code so the macro
knows who they are. This process will overwrite any existing data in the
sheets each time you use the macro.

Hope this is what you were looking for.

--
Regards...


"deedle93" wrote:

> I'm trying to figure out how I can automatically have data inserted into one
> worksheet copied to another based on an identifier. For example...In my
> first worksheet (i'll call it "all data") I want to use the first column to
> give each line item an identity (in this case either the letter "a" or the
> letter "b"). Once I've input all of my line items I want to have a formula
> that will automatically copy all the line item data for those lines
> identified with an "a" to a separate worksheet (I'll call it "A"). I want to
> be able to do the same for by "b" line items. In the end I should have three
> worksheets. the first contains all by "a" and "b" items in aggregate. The
> second is only the "a" items (as extracted by the formula using the "a"
> identified) and the third worksheet is the "b" items (as extracted by the
> formula using the "b" identifier). Can this been done? Could I reverse it
> and somehow input the data into the "a" and "b" worksheets and then use a
> command to aggregate them into the "all data" worksheet?

[deedle93]

Cincode...many thanks for the reply. I unfortunately am not familiar with
creating macros and VB (whatever that is), but I'm going to use your feedback
at the catalyst to learn how to do it and figure it out.

Much appreciated.

"cincode5" wrote:

> Deedle93
>
> This advice assumes you are somewhat familar with creating macros and VB.
>
> Try writing a VB Macro for each identifier (A, B, etc) that you want to
> create a unique list for. Make sure your 'All Data' Sheet has the
> Auto-Filter ON for the identifier column. The seperate macro codes read as
> follows:
>
> Sub Identifier_A() 'and B, and C etc.
>
> Selection.AutoFilter Field:=1, Criteria1:="a" 'and similarly for B, C,
> D, etc
> Columns("A:C").Copy 'these are the colums with data you want to copy
> Sheets("SheetA").Select 'Change "SheetA" to reference each Identifier tab
> Columns("A:C").Select 'same column references as above
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range("A1").Select
> Sheets("Sheet1").Select 'This returns you to your All Data sheet
> Application.CutCopyMode = False
> Selection.AutoFilter Field:=1
> Range("A1").Select
> End Sub
>
> You will initially need to create the tabs for each identifier. Make sure
> to name them identically to the names referenced in the VB code so the macro
> knows who they are. This process will overwrite any existing data in the
> sheets each time you use the macro.
>
> Hope this is what you were looking for.
>
> --
> Regards...
>
>
> "deedle93" wrote:
>
> > I'm trying to figure out how I can automatically have data inserted into one
> > worksheet copied to another based on an identifier. For example...In my
> > first worksheet (i'll call it "all data") I want to use the first column to
> > give each line item an identity (in this case either the letter "a" or the
> > letter "b"). Once I've input all of my line items I want to have a formula
> > that will automatically copy all the line item data for those lines
> > identified with an "a" to a separate worksheet (I'll call it "A"). I want to
> > be able to do the same for by "b" line items. In the end I should have three
> > worksheets. the first contains all by "a" and "b" items in aggregate. The
> > second is only the "a" items (as extracted by the formula using the "a"
> > identified) and the third worksheet is the "b" items (as extracted by the
> > formula using the "b" identifier). Can this been done? Could I reverse it
> > and somehow input the data into the "a" and "b" worksheets and then use a
> > command to aggregate them into the "all data" worksheet?