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

DanijelaS · Mar 10, 2009

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 · Mar 11, 2009

It prints only criteria that I want to display

Here is what it says in immidiate window:

SELECT `'Master $'`.`Skill Grade`, `'Master $'`.F2, `'Master $'`.F3,
`'Master $'`.F4, `'Master $'`.F5, `'Master $'`.F6, `'Master $'`.F7,
`'Master $'`.F8, `'Master $'`.F9, `'Master $'`.F10, `'Master $'`.F11,
`'Master $'`.F12, `'Master $'`.F13, `'Master $'`.F14, `'Master $'`.F15,
`'Master $'`.F16, `'Master $'`.F17, `'Master $'`.F18, `'Master $'`.F19,
`'Master $'`.F20, `'Master $'`.F21, `'Master $'`.F22, `'Master $'`.F23
FROM `'Master $'` `'Master $'`
WHERE (`'Master $'`.F4=3.0) OR (`'Master $'`.F4=4.0) OR (`'Master
$'`.F4=5.0)
ORDER BY `'Master $'`.F4
SELECT `'Master $'`.`Skill Grade`, `'Master $'`.F2, `'Master $'`.F3,
`'Master $'`.F4, `'Master $'`.F5, `'Master $'`.F6, `'Master $'`.F7,
`'Master $'`.F8, `'Master $'`.F9, `'Master $'`.F10, `'Master $'`.F11,
`'Master $'`.F12, `'Master $'`.F13, `'Master $'`.F14, `'Master $'`.F15,
`'Master $'`.F16, `'Master $'`.F17, `'Master $'`.F18, `'Master $'`.F19,
`'Master $'`.F20, `'Master $'`.F21, `'Master $'`.F22, `'Master $'`.F23
FROM `'Master $'` `'Master $'`
WHERE (`'Master $'`.F4=3.0) OR (`'Master $'`.F4=4.0) OR (`'Master
$'`.F4=5.0)
ORDER BY `'Master $'`.F4
SELECT `'MASTER skills grid$'`.F1, `'MASTER skills grid$'`.F2, `'MASTER
skills grid$'`.F3, `'MASTER skills grid$'`.F4, `'MASTER skills grid$'`.F5,
`'MASTER skills grid$'`.F6, `'MASTER skills grid$'`.F7, `'MASTER skills
grid$'`.F8, `'MASTER skills grid$'`.F9, `'MASTER skills grid$'`.F10,
`'MASTER skills grid$'`.F11, `'MASTER skills grid$'`.F12, `'MASTER skills
grid$'`.F13, `'MASTER skills grid$'`.F14, `'MASTER skills grid$'`.F15,
`'MASTER skills grid$'`.F16, `'MASTER skills grid$'`.F17, `'MASTER skills
grid$'`.F18, `'MASTER skills grid$'`.F19, `'MASTER skills grid$'`.F20,
`'MASTER skills grid$'`.F21, `'MASTER skills grid$'`.F22, `'MASTER skills
grid$'`.F23, `'MASTER skills grid$'`.F24, `'MASTER skills grid$'`.F25,
`'MASTER skills grid$'`.F26, `'MASTER skills grid$'`.F27, `'MASTER skills
grid$'`.F28, `'MASTER skills grid$'`.F29, `'MASTER skills grid$'`.F30,
`'MASTER skills grid$'`.F31, `'MASTER skills grid$'`.F32, `'MASTER skills
grid$'`.F33, `'MASTER skills grid$'`.F34, `'MASTER skills grid$'`.F35,
`'MASTER s
kills grid$'`.F36, `'MASTER skills grid$'`.`AVAILABILITY MATRIX`, `'MASTER
skills grid$'`.F38, `'MASTER skills grid$'`.F39, `'MASTER skills
grid$'`.F40, `'MASTER skills grid$'`.F41, `'MASTER skills grid$'`.F42,
`'MASTER skills grid$'`.F43, `'MASTER skills grid$'`.F44, `'MASTER skills
grid$'`.F45, `'MASTER skills grid$'`.F46, `'MASTER skills grid$'`.F47,
`'MASTER skills grid$'`.F48, `'MASTER skills grid$'`.F49, `'MASTER skills
grid$'`.F50, `'MASTER skills grid$'`.F51, `'MASTER skills grid$'`.F52,
`'MASTER skills grid$'`.F53, `'MASTER skills grid$'`.F54, `'MASTER skills
grid$'`.F55, `'MASTER skills grid$'`.F56, `'MASTER skills grid$'`.F57,
`'MASTER skills grid$'`.F58, `'MASTER skills grid$'`.F59, `'MASTER skills
grid$'`.F60, `'MASTER skills grid$'`.F61, `'MASTER skills grid$'`.F62,
`'MASTER skills grid$'`.F63, `'MASTER skills grid$'`.F64, `'MASTER skills
grid$'`.F65, `'MASTER skills grid$'`.F66, `'MASTER skills grid$'`.F67,
`'MASTER skills grid$'`.F68, `'MASTER skills grid$'`.F69, `'MASTER skills
grid$'`.F70,
`'MASTER skills grid$'`.F71, `'MASTER skills grid$'`.F72, `'MASTER skills
grid$'`.F73, `'MASTER skills grid$'`.F74, `'MASTER skills grid$'`.F75,
`'MASTER skills grid$'`.F76, `'MASTER skills grid$'`.F77, `'MASTER skills
grid$'`.F78, `'MASTER skills grid$'`.F79, `'MASTER skills grid$'`.F80,
`'MASTER skills grid$'`.F81, `'MASTER skills grid$'`.F82, `'MASTER skills
grid$'`.F83, `'MASTER skills grid$'`.F84, `'MASTER skills grid$'`.F85,
`'MASTER skills grid$'`.F86, `'MASTER skills grid$'`.F87, `'MASTER skills
grid$'`.F88, `'MASTER skills grid$'`.F89, `'MASTER skills grid$'`.F90,
`'MASTER skills grid$'`.F91, `'MASTER skills grid$'`.F92, `'MASTER skills
grid$'`.F93, `'MASTER skills grid$'`.F94, `'MASTER skills grid$'`.F95,
`'MASTER skills grid$'`.F96, `'MASTER skills grid$'`.F97, `'MASTER skills
grid$'`.F98, `'MASTER skills grid$'`.F99, `'MASTER skills grid$'`.F100,
`'MASTER skills grid$'`.F101, `'MASTER skills grid$'`.F102
FROM `'MASTER skills grid$'` `'MASTER skills grid$'`
WHERE (`'MASTER skills grid$'`.F7=3) OR (`'MASTER skills grid$'`.F7=4) OR
(`'MASTER skills grid$'`.F7=5)

DanijelaS · Mar 11, 2009

How do I get rid of the Raw 1 Column and have the appropriate heading. Currently it displays F1, F2, F3...

SkipVought · Mar 11, 2009

I guess you have no field headings. Makes it harder to understand.

You actually have TWO queries. ???

One is from sheet [highlight]Master [/highlight] (with a trailing SPACE) kind of sloppy!!! Has 23 columns. Criteria F4 is 3.0 or 4.0 or 5.0

The other is from sheet [highlight]Master skills grid[/highlight]. Has 102 columns. Criteria F7 is 3 ro 4 or 5.

do we have two separate source data sheets and two different queries?

What OTHER criteria do you have for subsequent queries?

Skip,
^{Don't let the Diatribe...

talk you to death!}_{Just traded in my old subtlety...

for a NUANCE!}

DanijelaS · Mar 11, 2009

I worked on two documents at the same time (just to practice) that’s why you can see two different sources. I do not really need both of them just one is okay. ( Master skill grid).

The other criteria would be print F8 if value is 3, or 4, or 5 on a new worksheet, again print F9 on a new worksheet if the value is 3, or 4, or 5 all the way until F27.

SkipVought · Mar 11, 2009

Simplified and modified, but still need the copy data to another sheet logic...

Code:

Sub GetSQL()
    Dim sSQL As String, i As Integer, ws As Worksheet
    
    Set ws = ActiveSheet
    
    For i = 7 To 27
        sSQL = sSQL & "SELECT *"
        sSQL = sSQL & "FROM `'MASTER skills grid$'`"
        sSQL = sSQL & "WHERE F" & i & " IN (3,4,5)"
    
        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

It seems that your Source Data is really a REPORT and that's making your life miserable.

Skip,
^{Don't let the Diatribe...

talk you to death!}_{Just traded in my old subtlety...

for a NUANCE!}

DanijelaS · Mar 11, 2009

I did again right click on the "Master Skill Grid" tab and pasted the coding. I am getting Run time errror '9' - Subscript out of range.

I was thinking to do data import for each colum and that way set up the separate sheets with conditions like I did for the first one (F7). Would that work?

DanijelaS · Mar 11, 2009

Criteria for F10, and from F20 until f27 should show only if value is equal to X.

Is there a way to have the heading column show?

SkipVought · Mar 11, 2009

I did again right click on the "Master Skill Grid" tab and pasted the coding.[/code]

Incorrect!

You must run this code in the sheet object where your QueryTable has been inserted. "Master Skill Grid" is the Source table.

Skip,
^{Don't let the Diatribe...

talk you to death!}_{Just traded in my old subtlety...

for a NUANCE!}

DanijelaS · Mar 11, 2009

I am copying the code just belowthe previous code of:

Sub GetSQL()
With ActiveSheet.QueryTables(1)
Debug.Print .CommandText
End With
End Sub

And again I got the Compile Error: Ambiguous Name detected:Get SQL

Is this because the sub name is the same or I again paste it in the wrong place.

SkipVought · Mar 11, 2009

Name the last procedure I sent you something different.

Skip,
^{Don't let the Diatribe...

talk you to death!}_{Just traded in my old subtlety...

for a NUANCE!}

DanijelaS · Mar 11, 2009

Now, I receive a message in excel that " Could not decrypt file." I have option OK and Cancle, when I select OK it prompts me to select the source file. I really messed this up..
Initially I worked on the version 3 and I resaved the version to 4 after I got the first code working.

When I chenge the source selection to versin 4 I get the error in code - Run time Error '1004' - Application-defined or object-defined error.

SkipVought · Mar 11, 2009

The QueryTable has the ORIGINAL CONNECTION string in it. It is pointing to the original workbook (version whatever???)

I'd suggest deleting the querytable and readding. Turn on your macro recorder and record adding the querytable, in case you must do it again.

There is alos a way to CHANGE the connection string to the CURRENT workbook. I could show that to you also, if you care to have.

Skip,
^{Don't let the Diatribe...

talk you to death!}_{Just traded in my old subtlety...

for a NUANCE!}

SkipVought · Mar 11, 2009

Here's some code that points the connection to the CURRENT WORKBOOK...

Code:

Sub GetSQL()
    Dim sSQL As String, i As Integer, ws As Worksheet
    Dim sPath As String, sDB As String, sConn As String
    
    sPath = ThisWorkbook.Path
    
    sDB = Split(ThisWorkbook.Name, ".")(0)
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
    
    Set ws = ActiveSheet
    
    For i = 7 To 27
        sSQL = sSQL & "SELECT *"
        sSQL = sSQL & "FROM `'MASTER skills grid$'`"
        sSQL = sSQL & "WHERE F" & i & " IN (3,4,5)"
    
        With ws.QueryTables(1)
            .Connection = sConn
            .CommandText = sSQL
            .Refresh
        End With
        'now copy the data on this sheet to another sheet
        ws.[A1].CurrentRegion.Copy _
            Destination:=Sheets("Sheet" & i).[A1]  'this assumes that you have sheets named Sheet7, Sheet8, ...Sheet27
    Next
    
    Set ws = Nothing
End Sub

Also does a COPY to another sheet in the workbook

Skip,
^{Don't let the Diatribe...

talk you to death!}_{Just traded in my old subtlety...

for a NUANCE!}

DanijelaS · Mar 11, 2009

I have deleted all macros and wanted to start from beginning. However it is taking forever for query to finish the last step (Return Data to Microsoft Office Excel).

SkipVought · Mar 11, 2009

Have a cup of coffee. Then reboot!

Skip,
^{Don't let the Diatribe...

talk you to death!}_{Just traded in my old subtlety...

for a NUANCE!}

DanijelaS · Mar 11, 2009

It still not working I have rebooted twice already.

SkipVought · Mar 11, 2009

???

Skip,
^{Don't let the Diatribe...

talk you to death!}_{Just traded in my old subtlety...

for a NUANCE!}

DanijelaS · Mar 11, 2009

I have copied the file on removable disk and when I do Data, import external data I get the message that data does not contain reportable columns.

SkipVought · Mar 11, 2009

using the code that includes reassigning the Connection string?

Skip,
^{Don't let the Diatribe...

talk you to death!}_{Just traded in my old subtlety...

for a NUANCE!}

DanijelaS · Mar 11, 2009

I am back at step where I input code of:
Sub GetSQL()
Dim sSQL As String, i As Integer, ws As Worksheet

Set ws = ActiveSheet

For i = 7 To 27
sSQL = sSQL & "SELECT *"
sSQL = sSQL & "FROM `'MASTER skills grid$'`"
sSQL = sSQL & "WHERE F" & i & " IN (3,4,5)"

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

------------------------------------------
I am getting error message Run-time error '1004' - Application defined or object defined error

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

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

IS-IT--Management

IS-IT--Management

IS-IT--Management

Programmer

IS-IT--Management

Programmer

IS-IT--Management

IS-IT--Management

Programmer

IS-IT--Management

Programmer

IS-IT--Management

Programmer

Programmer

IS-IT--Management

Programmer

IS-IT--Management

Programmer

IS-IT--Management

Programmer

IS-IT--Management

Similar threads

Log in

Part and Inventory Search

Sponsor