How to copy cells from one worksheet to another if the value in ra

S

sgtpsychosis

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?
 
M

Mike

The fastest way would be to filter by Column E. Then Copy and paste on the
worksheet you want.
 
L

Lookup formula limitation?

Mike,
Do you know why, when I copy and past from file one A1 has 1000 words in it
to another file B2, B2 shows only 35 words out of 1000?
 
O

Otto Moehrbach

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
 
O

Otto Moehrbach

Are you the psychotic Sgt or are you a new OP? If the latter, describe in
detail what you are trying to do, what you do, what Excel does, and what you
want Excel to do. If the former, same questions. HTH Otto
"Lookup formula limitation?"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top