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!

 

[SkipVought]

Hi,

I'd use MS Query, via Data > Import External Data.... Pretty quick.

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

 

[DanijelaS]

Thanks SkipVought!

Can you please provide more information how this approach would work.

Thanks again!

 

[SkipVought]

Assuming that you have added a querytable to sheet2 and your source data is on sheet1...

dim s as string
s = "Select * "
s = s & "From [Sheet1$] "
s = s & "Where YourField='" & YourCriteria & "'"

With Sheet2.Querytables(1)
  .CommandText = s
  .Refresh false
end with

YourCriteria could be the value in a cell in sheet2

s = s & "Where YourField='" & sheet2.[A1] & "'"

This can also be done with a parameter query,without using VBA code. However, more complex criteria using LIKE or IN structures need to ve built in VB.

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

 

[DanijelaS]

Even with your great help I have no idea how to make this work. I thought it was much easier.
Is there any books/websites you can recommend that have tutorials on this subject?

 

[SkipVought]

No.

What have you tried?

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

 

[DanijelaS]

I am not sure where to input the codes you have written.

 

[SkipVought]

Did you try,

I'd use MS Query, via Data > Import External Data.... Pretty quick.

???


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

 

[DanijelaS]

I went Data, Import External Data, Import Data and then the window to Select Data Source appeared. I have selected the same file and choose to import Sheet 1 (which is my master data) starting at cell A1. This way everything was copied. Now, I need to edit the query but not sure what to select to input the coding:
CODE
dim s as string
s = "Select * "
s = s & "From [Sheet1$] "
s = s & "Where YourField='" & YourCriteria & "'"

With Sheet2.Querytables(1)
.CommandText = s
.Refresh false
end with

 

[SkipVought]

Ignore the code for now.

Just use the query grid to get the data that you need to return for the first set of criteria.

Then File > Return data to Excel.

When you get that far, post back for the next step. This is all a ONE TIME exersize to get things set up properly.

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

 

[DanijelaS]

I noticed that some of the heading columns did not populate. and raw 1 is populated with F1, F2, F3....

When you say,"Just use the query grid to get the data that you need to return for the first set of criteria."
It means I delete the raws I don't need and save it.

 

[SkipVought]

All you do is drag the columns you want into the QBE grid and set the criteria value(s). Then File > Return data to Microsoft Excel.

The query is in the querytable object and need not be "saved".

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

 

[DanijelaS]

When I did this step of Data, Import External data, Import Data, selected the worksheet and clicked okay the new worksheet is created which is already in excel and there is no QBE grid that you are referring to. I think I am missing the crucial step.

 

[SkipVought]

Data > Import External Data > New database query...

Select the Excel Workbooks Driver

Drill down to your workbook

Select your sheet.

Edit the query

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

 

[DanijelaS]

Wow, it works now


How does the information updates if the values change on the master tab.

 

[SkipVought]

OK. Now lets get your SQL code where we can work on it.

Right-click on the sheet tab and select View Code.

Paste this code into the code sheet and RUN.

sub GetSQL()
  with activesheet.querytables(1)
    debug.print .commendtext
  end with
end sub

Post back the SQL from the Immediate Window (View > Immediate)

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

 

[DanijelaS]

I am getting Run Time error '438' - Object doesn't suport this property or method.

Just to mention that when I selected my tables I also inputed the criteria column F7 equals value of 3, or 4, or 5. This might have influence the Run Time error.

 

[SkipVought]

Sorry I miss spelled .CommandText

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

 

[DanijelaS]

It works now!

Post back the SQL from the Immediate Window (View > Immediate) - this only gives the split window at the bottom.

 

[SkipVought]

debug.print writes the contents of the variable into this window.

SCROLL to find.

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