next for each copy data based on criteria

twvanduzee · Jun 7th 2021

Hello

My last thread worked like a charm. However, it seems management has changed their mind about how they would like to see data.

1. I have data from A to R and approx 1200 rows.

2. I sort by column P in ascending order

3. I created 7 Sheets for the main departments

4. In the P column I have sub categories of all the main departments, which must be copied/pasted to the appropriate sheet..

Code

Sub SeparateSheetData()
Dim rng As Range
Dim rng2 As Range
Dim ws As Worksheet
lastr = ThisWorkbook.Worksheets("Alldata").Cells(Rows.Count, "p").End(xlUp).Row
Set ws = ThisWorkbook.Sheets("Alldata")
Set rng = ws.Range("v2:v11")
Set rng2 = ws.Range("p2:p" & lastr)
Worksheets("Alldata").Range("a1:r" & lastr).Sort key1:=Range("p1"), Order1:=xlAscending, Header:=xlYes
For Each cll In rng.Cells.Value
    For Each c In rng2.Cells.Value
        If c = cll Then
            With Worksheets("Alldata").Range("A2:r" & lastr)
            .AutoFilter Field:=16, Criteria1:=c '& strSearch & "*"
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells.Copy
            Sheets("Community Services").Activate
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            End With
        
        Worksheets("Alldata").Activate
        Worksheets("Alldata").ShowAllData
        Else:
        Exit For
        End If
    Next c
Next cll
End Sub

Display More

This works kinda sorta but as you can see, I have to hard code the sheet name.

Also, it iterates through all the values in the sheet for each value in my list.

And of course, the data needs to be appended to the last row on the appropriate sheet.

Any idea how I can make this happen?

Thank You

Terry

gijsmo · Jun 8th 2021

You mention A:R contains the data but the code is also looking at V2:V11 - what does V2:V11 contain ?

If it contains main department name then that would be more than the 7 sheets you mention.

When you say sub-category, is there a column for main department in A:R and another column representing sub-category?

Your autofilter uses a variable called strSearch but the code provides no idea of what this value could be.

A sample workbook with some (non-sensitive) data and an example of what the output should look like is always helpful.

**royUK** · Jun 8th 2021

Attach an example workbook.

twvanduzee · Jun 8th 2021

The v column contains values I typed in, they are sub categories that belong to "Community Services

"sampledata.xlsx

Each sub category of needs to go on it's appropriate sheet.

Col Z contains the a list of unique values for all the Primary divisions.

The xl book only contains a small number of rows in the alldata sheet.

See Attachment

Thank You

Terry

gijsmo · Jun 9th 2021

Okay, that helps a bit.

I can see there are 7 sheets from Community Services through to Senior Management. However, your columns V to X only seem to cover the first 3 of these.

I am assuming the list of names under each of the columns V to X contain the "sub-categories" you were referring to. But it's not entirely clear where these sub-categories can be found in the main data. Also, I would have expected 7 of these in total with their sub-categories listed in full eg, the Executive column has no sub-categories listed and of course there are no columns for the others eg, Infrastructure, etc.

Column P seems to mostly represent the "Primary Division" which I've assumed would correspond to 1 of the 7 sheet names. However, there are items in the sample in column P like "Engineering" and "FCSS" that seem to be sub-categories but there is no corresponding primary category (and therefore no idea which sheet) these belong to. It seems column P could contain either a primary or sub-category value.

It may be an idea to hold the full list of the 7 main departments and their sub-categories in another sheet, just to separate it from the main data. Even if it is retained on the Alldata sheet, I would expect the column headers will need to match the sheet names exactly eg, at the moment cell V1 = Comm Services but the corresponding sheet is actually Community Services.

You also mention "the data needs to be appended to the last row on the appropriate sheet" when you run the macro. However, you either then need to remove the data from the Alldata sheet or add another column that flags that the data has been copied otherwise the data will get copied again if you run the macro more than once.

twvanduzee · Jun 9th 2021

Thank You for your reply, I looked at the workbook, and I have included all info that will be needed (even though the Alldata sheet only has 100 or so records, there are actually over 1200 in the spreadsheet. This will end up being a report every week (hense the need to automate).

I will end up removing the B, C, E, G, H, K, L, M, N, Q columns before distributing the data to other sheets.

I have "re-organized" the workbook.

I created a sheet called "Needed Info" that breaks down each department and which "Title" goes on what sheet.

I have created a sheet called "Com Breakdown" that will breakdown "Community Services" into Managers. For the community services sheet. Example-Managers.xlsx

I really appreciate the help with this, Thank you

gijsmo · Jun 10th 2021

OK, there is more information in the new example book but it's still not quite clear how you want to split up the data.

You have only 7 main departments and there is a sheet for each:

However, you seem to want to somehow use Column P to split up the data into those 7 sheets.

In the new example sheet you attached, the following are the unique values in Column P on the Alldata sheet:

So there seems to be a "lookup table" missing eg which of the 7 sheets does the data for Engineering, Human Resources, IST, etc actually need to be copied to. Some rows in the sample do have a matching sheet name already eg Marketing_Communications.

What I was excepting see was something like a table with all the sub-categories on the left and the corresponding sheet name on the right eg:

The left side would of course have to include all possible sub-categories, the above example is just derived from your sample.

The coding is not too difficult but without a table like this it is difficult to start using the sample data in Column P.

Also, as per previous post, you also mention "the data needs to be appended to the last row on the appropriate sheet" when you run the macro. However, you either then need to remove the data from the Alldata sheet or add another column that flags that the data has been copied otherwise the data will get copied again if you run the macro more than once.

You now also say "I will end up removing the B, C, E, G, H, K, L, M, N, Q columns before distributing the data to other sheets." but this could be relatively easily done as part of the macro that copies the data to the 7 sheets.

twvanduzee · Jun 10th 2021

I apologize for any confusion:

On the "Needed Info" sheet:

Community Services, Corporate Services, Executive etc are column headings.

Each value under each column heading matches the values in the O column of the "Alldata" sheet.

I need to find all occurances of each value on "Needed Info" on the "Alldata" sheet and copy the data to the Appropriate sheet.

Each column heading on "Needed Info" matches a sheet name. So ever value under the column heading needs to go to the corresponding sheet.

Ex: sheet "Needed info": Under the column of "Community Services" are several values (Assistant Fire Chief, Bi-law Enforcement, Casual By-law Officer ....etc).

Each of these values exist in the O column. (Title)

Filter the "Alldata" sheet for "Assistant Fire Chief", copy all the results to the "Community Services" sheet. This would be done for all values under all columns of the "Needed Info" sheet.

Ex: sheet "Needed Info". Column "Executive". Look up each value (CAO, Council, Mayor) in the P column of "Alldata". Filter based on the value, Copy to the Executive sheet.

I made the column headings on the "Needed info" sheet the same as the sheet name in an attempt to make it easier to find the sheet to copy to.

I hope this adds a little clarity.

This code is fairly convoluted, But Im trying to figure it out as I go along:

Code

Sub transferdata()
Dim dRng As Range 'range for department headings on "Needed Info" sheet
Dim aRng As Range 'range for data on Alldata sheet
Dim dWs As Worksheet 'Needed Info Worksheet
Dim aWs As Worksheet 'AllData Worksheet
Dim aLastrow As Long
'Dim cRow As Double
Dim Number As Long
Dim cLetter As String
Dim vRow As Long

Set aWs = ThisWorkbook.Sheets("Alldata")
Set dWs = ThisWorkbook.Worksheets("Needed Info")
Set dRng = dWs.Range("A1:G1") 'just to give it a value
aLastrow = aWs.Cells(Rows.Count, "A").End(xlUp).Row
Set aRng = aWs.Range("O1:O" & aLastrow)

'Set the range for each column (1 per iteration)
For Each c In dRng
V = c.Value
vRow = 0
    cNumber = c.Column
    cLetter = Split(Cells(1, cNumber).Address, "$")(1)
    vRow = Cells(Rows.Count, cLetter).End(xlUp).Row
    Set dRng = Range(cLetter & "2:" & cLetter & vRow)  'New dRng
    'MsgBox dRng.Address
        For Each cll In dRng  ' iterate through values in the dRng column
            aWs.Activate
            With aWs.Cells
.AutoFilter 15, cll
.Offset(1).SpecialCells(12).Copy aWs.Cells(Rows.Count, 1).End(xlUp).Offset(1)
End With
        Next cll
    Set dRng = dRng.Offset(0, 1) ' Advance column to the next column
    
Next c
End Sub

Display More

Thank You

Terry

twvanduzee · Jun 10th 2021

I have it working (kinda).

It separates the data the way I want it to (Except breaking down the Community Services sheet).

Here's the code I've come up with -- likely needs some finessing.

Code

Sub transferdata()
Dim dRng As Range 'range for department headings on "Needed Info" sheet
Dim aRng As Range 'range for data on Alldata sheet
Dim dWs As Worksheet 'Needed Info Worksheet
Dim aWs As Worksheet 'AllData Worksheet
Dim aLastrow As Long
'Dim cRow As Double
Dim Number As Long
Dim cLetter As String
Dim vRow As Long

Set aWs = ThisWorkbook.Sheets("Alldata")
Set dWs = ThisWorkbook.Worksheets("Needed Info")
Set dRng = dWs.Range("A1:G1") 'just to give it a value
aLastrow = aWs.Cells(Rows.Count, "A").End(xlUp).Row
Set aRng = aWs.Range("A1:R" & aLastrow)

'Set the range for each column (1 per iteration)
For Each c In dRng
V = c.Value
vRow = 0
    cNumber = c.Column
    cLetter = Split(Cells(1, cNumber).Address, "$")(1)
    vRow = Sheets("Needed Info").Cells(Rows.Count, cLetter).End(xlUp).Row
    Set dRng = Sheets("Needed Info").Range(cLetter & "2:" & cLetter & vRow)
    'MsgBox dRng.Address
        For Each cll In dRng
            aWs.Activate
            With aRng
    'MsgBox aRng.Address
                .AutoFilter 15, cll
                .Offset(1).SpecialCells(12).Copy Sheets(c.Value).Cells(Rows.Count, 1).End(xlUp).Offset(1)
                Worksheets("Alldata").ShowAllData
            End With
        Next cll
    Set dRng = dRng.Offset(0, 1)
'   Stop
Next c
End Sub

Display More

I still need to remove the columns, autocolumn width and create workbooks for each of the Manager names (community Services).

But, so far it seems to be going well.

Thank you

Terry

gijsmo · Jun 11th 2021

My sample code is attached which should do what you are trying to do if I have interpreted everything in these posts so far

There are 2 main macros called TransferData and ClearSheets. The Transferdata macro does the following:

Read each of the 7 main categories in the Needed Info sheet, one column at a time
Find the matching rows in the Alldata sheet for all the values in the category (eg, Community Services has 26 sub-categories, Executive has 3 etc)
Copy the matching data to the corresponding category sheet (excluding B, C, E, G, H, K, L, M, N, Q columns)
Minor formatting (auto column widths, freeze panes)
Sort the data by Title and Name in ascending order

This means every time you run the macro, the data is recreated on the category sheets. As your sample data is limited, some sheets will end up with just a header row with no actual data rows when you run the Transferdata macro.

ClearSheets can be used if required, it will just delete everything in the category sheets. Not strictly required as the TransferData macro will do this anyway. I have added another button on the Summary sheet that will run this macro.

Example-Managers.xlsm

gijsmo · Jun 11th 2021

Please note also that you may need to do some data correction. I noticed there were records in the Alldata sheet that will not match to any of the sub-categories specifically, "Handi Bus Driver" (this should probably be "Handibus Driver" by the look of it) and also "Recreation"

Also, I happened to notice that eg "Parks Manager" and "Recreation Programming Manager" appear under both Community Services and Senior Management which means any entries for these will be duplicated across the 2 corresponding sheets. I can see some others, this may be intentional.

next for each copy data based on criteria

Participate now!

Share

Users Viewing This Thread