Sgt
Mike gave you a worksheet solution that works well. But you said
"automatically" That implies you want Excel to do all the work. That is
called VBA programming. The following macro does all the filtering,
copying, and pasting for you. Come back if you need more. HTH Otto
Sub ShuffleData()
Dim TotalRng As Range
Dim wg As Variant
Dim RngToCopy As Range
Set TotalRng = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(,
10)
Set RngToCopy = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(,
10)
Application.ScreenUpdating = False
TotalRng.AutoFilter
For Each wg In Array("WG1", "WG2", "WG3", "WG4")
On Error GoTo NoData
TotalRng.AutoFilter Field:=5, Criteria1:=wg
With Sheets(wg)
RngToCopy.SpecialCells(xlCellTypeVisible).Copy
.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
End With
NoData:
On Error GoTo 0
Next wg
TotalRng.AutoFilter
Application.ScreenUpdating = True
End Sub
"sgtpsychosis" <(E-Mail Removed)> wrote in message
news:89805569-8853-43AD-9EF2-(E-Mail Removed)...
>I have a large amount of data on one worksheet. The data on this worksheet
> applies to all the personnel in the entire organization.
> One column in this worksheet identifies which workgroup each person is a
> member of (Column E, values are "WG1","WG2","WG3", and "WG4").
> I have created worksheets for the separate workgroups.
> How can I have the data specific for each workgroup automatically copied
> from the total organization worksheet to each working groups individual
> worksheet, based on the value in Column E?
|
Similar Threads
