Excel - copy data from one worksheet to another under criteria 2

[DanijelaS]

Hi,
How do I copy data from one worksheet to another only if it applies certain conditions. For example, I have a master data where I have a list of employees and their skills divided by colums. Each employee may have one or multiple skill set and I want to have a separate tab that only copies employees where skill set is equal to value if it is blank than do not copy. This way when I change any value on the master tab other tabs will be copied?

Thank you!

 

[DanijelaS]

I have uploaded the file I was working with, if you have time could you please take a look.

 

[SkipVought]

Where is your query Table???

Your data structure is absolutely HORRIBLE. It is a REPORT, not a well contructed table. In fact, MS Query cannot handle that many columns. AWFUL!

Do you have the source data that this report was generated from? THAT is what you could and SHOULD work with.

I pared down the query and it worked ffom 7 to 9, but 10 has NO NUMERIC DATA, so it burped.

Sub GetSQL()
    Dim sSQL As String, i As Integer, ws As Worksheet
    
    Set ws = ActiveSheet
    
    For i = 7 To 27
        sSQL = "SELECT"
        sSQL = sSQL & " F1"
        sSQL = sSQL & ",F2"
        sSQL = sSQL & ",F3"
        sSQL = sSQL & ",F4"
        sSQL = sSQL & ",F5"
        sSQL = sSQL & ",F6"
        sSQL = sSQL & ",F7"
        sSQL = sSQL & ",F8"
        sSQL = sSQL & ",F9"
        sSQL = sSQL & ",F10"
        sSQL = sSQL & ",F11"
        sSQL = sSQL & ",F12"
        sSQL = sSQL & ",F13"
        sSQL = sSQL & ",F14"
        sSQL = sSQL & ",F15"
        sSQL = sSQL & ",F16"
        sSQL = sSQL & ",F17"
        sSQL = sSQL & ",F18"
        sSQL = sSQL & ",F19"
        sSQL = sSQL & ",F20"
        sSQL = sSQL & ",F21"
        sSQL = sSQL & ",F22"
        sSQL = sSQL & ",F23"
        sSQL = sSQL & ",F24"
        sSQL = sSQL & ",F25"
        sSQL = sSQL & ",F26"
        sSQL = sSQL & ",F27"
        sSQL = sSQL & ",F28"
        sSQL = sSQL & ",F29"
        sSQL = sSQL & ",F30 "
        sSQL = sSQL & vbLf
        sSQL = sSQL & "FROM `'MASTER skills grid$'` "
        sSQL = sSQL & vbLf
        sSQL = sSQL & "WHERE F" & i & " IN (3,4,5)"
    
        Debug.Print sSQL
    
        With ws.QueryTables(1)
            .CommandText = sSQL
            .Refresh
        End With
        'now copy the data on this sheet to another sheet
        
    Next
    
    Set ws = Nothing
End Sub

Her is the result for F9...
[tt]
F1 F2 F3 F4 F5 F6 F7 F8 F9
F10 F11 F12 F13 F14 F15 F16 F17 F18
F19 F20 F21 F22 F23 F24 F25 F26 F27
F28 F29 F30

LR DH Name 5 PR 5 4 4
4
X X X X X X x
x Auto/p@w

LR BI Name 14 PR 5 3
3 3 X
X X X X X
X ALL

LR DH Name 19 PR 7 5 5
5
X X X X X X x
x P

LR DH Name 20 PR 7 4 4
4 X
X X X X X X x
x P

LR DH Name 21 PR 5 3 3
3
X X X X X X x
x P

LR DH Name 22 PR 7 4
4 X
X X X X X X x
x P

LR DH Name 23 PR 7 5
5 X
X X X X X X x
x P

CL GS Name 36 HR 9 5 5
X 5 5 X
X X X X X X X
X West P

CL GS Name 44 PR 7 4 4
X
X X X X
X West S

CL GS Name 46 PR 7 5 5
X X X
X X X X X X X
X West P/S

CL GS Name 48 PR 7 5
5 X
X X X X X X X X
X Central S

CL IM Name 61 PR 7 4
4 4 x
X X X X X X X
X WINN

CL IM Name 62 PR 7 5
5 5 x
X X X X X X X X
X

CL IM Name 63 PR 7 5
5 5 x
X X X X X X X X
X

CL IM Name 64 PR 7 5
5 5 x
X X X X X X X
X

CL IM Name 65 PR 7 4
4 4 x
X X X X X X X
X

CL IM Name 66 PR 9 5
5 5 x
X X X X X X X X
X WINN p

LR NB Name 81 PR 5 3 3
3 x
x x x x x x
x Vancouver s

LR NB Name 82 PR 7 3
3 x
x x x x x x
x Vancouver s

LR NB Name 83 PR 7 4
4 4 x
x x x x x x
x Vancouver s

LR NB Name 84 PR 7 3
3 x
x x x x x x
x Vancouver s

LR NB Name 85 PR 7 4
4 4 x
x x x x x x
x Vancouver s

LR SR Name 90 PR 7 5 5
5 5 X
X X X X X X X X
X TOR P

LR SR Name 91 PR 7 5 5
4 4 X
X X X X X X X X
X TOR s

LR SR Name 92 PR 7 5 5
4 X
X X X X X X X X
X TOR s

LR SR Name 93 PR 7 4 4
4 X
X X X X X X X
X TOR s

LR SR Name 94 PR 9 5 5
X 5 X X
X X X X X X X
X TOR P

LR SR Name 95 PR 9 5 5
X 5 X X
X X X X X X X
X TOR P

LR SR Name 96 PR 7 5 5
X 5 X X
X X X X X X X
X TOR P

LR SR Name 97 PR 7 5 5
x 5 X X
X X X X X X X
X TOR P

LR SR Name 98 PR 7 4
4 4 X
X X X X X X X X
X TOR P

LR SR Name 99 PR 7 4
4 4 X
X X X X X X X
X TOR

LR SR Name 100 PR 7 4
4 3 X
X X X X X X X X
X TOR

LR SR Name 101 PR 7 3
3 2 X
X X X X X X X
X TOR

[/tt]

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[DanijelaS]

This was generated manually, would it help if I change all alpha (X) to numeric?

 

[SkipVought]

The data is what the data is.

It seems that with this absolutely horrible non-normalized table structure, that MS Query cannot handle that many columns.

As an alternate approch, use the AutoFilter.

You ought to be able to macro record setting two criteria on ONE field ( >=3 AND <= 5 ), and then clearing/setting in a loop as we had in the past code post. Once a filter is set, copy the VISIBLE cells and paste in the appropriate sheet. Pretty straight forward.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[DanijelaS]

Thank you so much for all your help!

I'll use the filter option.

In your opinion, would it be difficult to set up tables in access and have structure build so that when I run report I would be able to capture report that will have a data organized in a way like provided excel spreadsheet.

 

[SkipVought]

I see no point in perpetrating such a dreadful table design.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[DanijelaS]

I might not be dreadfull if I had your help.

 

[SkipVought]

How do you get this report?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[DanijelaS]

manually. Basically, going from person to person and asking if they have that skill or not. If they did then base on the knowledge I will add the level.

 

[SkipVought]

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Your table, out thru column 30 is probably OK.
Your weekly matrix is what makes this horrible as a TABLE, not a REPORT.

Do you generate this data manually, too?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[mscallisto]

Skip

a star for your perseverance plus I picked up a few tips.

thanks

sam

 

[SkipVought]

Sam,

Isn't that what Tek-Tips is all about!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[mscallisto]

absolutely