Populating multiple worksheets from one main sheet

[MIAnalyst]

I apologuse if this has been asked before but I have been looking and cannot find the answer I need.

Here's the scenario

I have a main sheet which will act as the input sheet.

this sheet is populated with the following info:

First Column - Name
Second Column - Department
Third (section a) - Twelth (section j) column has an x populating some fields

ie Person 1 has an x in section a, e, f & j
Person 2 has an x in d, h & i

etc

I need a worksheet for each section and when data is inputted into the main sheet, there is an update button which then populates each worksheet so that section a shows only those with an x in that column and so on.

Can anyone help please, have hit a bit of a flat spin

TIA

 

[SkipVought]

Hi,

Your processing would be ALOT easier if your Main Sheet table were NORMALIZED.
[tt]
Name Department Section Name
Person 1 XYZ Sec a
Person 1 XYZ Sec e
Person 1 XYZ Sec f
Person 1 XYZ Sec j
...
[/tt]
"...ie Person 1 has an x in section a, e, f & j
Person 2 has an x in d, h & i..."

Rather Person 1 has 4 rows, Person 2 has 3 rows etc.

Getting the data into each sheet could then easily be done with a Pivot Table or a QUERY faq68-5829

Skip,

_[red][/red]

 

[MIAnalyst]

Thanks very much SkipVought

 

[MIAnalyst]

MS Query won't work - therre's too many fields

 

[SkipVought]

??? How many fields do you have? Your post suggested that you have 10 in your original format. My suggestion would result in 3. ???

Skip,

_[red][/red]

 

[MIAnalyst]

I have 300 rows and 41 columns which will then populate 34 separate worksheets.

Basically I think I need to check the row for data, if there is an x in the column then copy and paste that row into worksheet. Just not entirely sure how to check a row for data. Got the select row, copy and paste into new worksheet okay, just not the check for data

 

[xlbo]

You can achieve this with AUTOFILTER

You would loop through the columns, applying the autofilter for "=X".
You then simply copy and paste all rows to the appropriate sheet - the nice thing about autofilter is that when a row is hidden it does not get copied across

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MIAnalyst]

Problem fixed - used this:

Sub update()

Application.ScreenUpdating = False

Dim I
p = 2
I = Sheet2.Rows.Count

Sheets(2).Select

If I > 300 Then I = 300

For c = 1 To 34

For x = 1 To I
    j = x + 1

    If Worksheets(2).Cells(j, c + 8) = "x" Then
        Worksheets(2).Range(Cells(j, 1), Cells(j, 42)).Select
        Selection.Copy
        Sheets(c + 3).Select
        Sheets(c + 3).Range(Cells(p, 1), Cells(p, 42)).Select
            ActiveSheet.Paste
        p = p + 1
        Sheets(2).Select
        Application.CutCopyMode = False
    End If
Next x
p = 2
Next c

Sheets(1).Select

End Sub

 

[xlbo]

That will work but will take a lot longer than using the autofilter method - you can also use the "Destination" argument opf the Copy method so that the data is not put on the clipboard and copies quicker:

Have a play with this to see what I mean:

For I = 2 To 4
    lRow = 300
    Range("A1:E" & lRow).AutoFilter Field:=I, Criteria1:="X"
    Range("A2:A" & lRow).Copy [b]Destination:=Sheets(Cells(1, I).Value).Cells(1, 1)[/b]
    Range("A1:E" & lRow).AutoFilter
Next I

This code was mocked up with people's names in col A and Xs in random positions in the next 4 columns with column headers A to D in row 1 and sheets set up with names A to D

Hope that makes sense



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bdmangum]

You could speed the macro up a lot by removing the .Select commands. Try something like the code below:

If Worksheets(2).Cells(j, c + 8).Value = "x" Then
    For counter = 1 To 42
        Sheets(c + 3).Cells(p, counter).Value = Worksheets(2).Cells(j, counter).Value
    Next
    p = p + 1
End If

BD

 

[MIAnalyst]

The macro I wrote the above code for takes less than 20 seconds to run whereas when I did the autofilter method, it ran then crashed my spreadsheet.

Thanks for all your help though

 

[bdmangum]

Considering you have only 300 rows and 34 sheets, 20 seconds is a long time for a macro to complete your task. If you would remove the select/copy/select/paste coding and replace it with the code I posted above you will cut the macro run time in half.

BD

 

[MIAnalyst]

Okay, maybe I was overexaggerating slightly!

I'm finishing for the evening now but will have a go at your code tomorrow.

Thanks again