Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
deedle93
 
Posts: n/a
Default Sharing data across worksheets within a workbook based on identifi

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?
  #2   Report Post  
cincode5
 
Posts: n/a
Default

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?

  #3   Report Post  
deedle93
 
Posts: n/a
Default

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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inserting Filtered RC cell information into other worksheets Dennis Excel Discussion (Misc queries) 10 July 30th 05 01:54 AM
Getting data from another workbook based on variable joemc911 Excel Discussion (Misc queries) 3 May 25th 05 09:18 AM
Match and index functions: corrlating data from 2 worksheets [email protected] Excel Worksheet Functions 2 May 21st 05 05:38 AM
Too much data to save a workbook Tobie Hanekom New Users to Excel 0 May 16th 05 08:55 AM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 06:55 PM


All times are GMT +1. The time now is 01:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"