Pull specific data from one work sheet to a new workbook

[Danny]

Hi Everyone

I have a problem to which I cannot find the answer for love nor money!

I am creating a macro for a set of interviews, the questions are predefined however they change depending on the profile of the person being interviewed: IE the CEO has a different set of questions to the CFO etc.

I have a sheet in my workbook where all the questions are stored. This will be attached to allow you to see the layout. A X is present for questions that need to be asked for each profile.

I am trying to create a macro that will scan through, detect all the boxes that contain a X under a given profile (IE all questions for the CEO) and display them into a new workbook.

Is this possible?

If you have any questions and require me to explain it further then please ask. I know when reading posts it isn't always clear.

 

[Sathish KV]

Hi,

Few questions on your requirement...

1) Is the output required in a seperate sheet
2) Do you need only the questions are other columns too
3) Where will you mention the profile name to retrieve the quetions releated to the profile
4) Do you work with macros

 

[Danny]

Hi Sathish

Thank you for getting back to me so quickly.

1) The output is required in a new workbook.
2) Mainly the questions, however the importance would be useful.
3) the profile name is on another sheet, i click on a specific cell and it pulls all the required data.
4) Yes, working with multiple macros and VBA

So, the end result would be:

I click on the link (shown below, not a hyperlink)
It pulls:
Interviewee
Profile and Role

From the sheet below



Then it pulls the questions and the importance from the questions tab.

This is then put together in a new workbook.

Many Thanks
Danny

 

[Sathish KV]

Hi,

Could you please provide an excel file with outline

i.e. Sheet 1 with Data 1 headers only (Profile Database), Sheet 2 with Data 2 headers only (Question Database) and Sheet 3 with the Output heders only

 

[Danny]

Hi,

Just a quick brief: Contact list will contain data about the interviewees
Interviews: Details about the interviews
Interview_Questions - self explanatory
Output - The content I would like displayed in a new workbook (not worksheet)

Thank you

 

[Sathish KV]

Hi,

Try the below code to retrieve questions based on the role in the Sheet Interview

Sub Questions()
  Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, strConn As String, Header As Boolean
  Set cn = New ADODB.Connection
  Dim Role1 As String
  pth = ActiveWorkbook.FullName
  Role1 = ActiveCell.Offset(0, -7).Value
 
  strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=" & pth & ";" & _
  "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
  cn.Open strCon
'Creating a sql query with required columns and criterias
  strQuery = "SELECT [Question] FROM [Interview_Questions$B4:V25] WHERE [" & Role1 & "]='x';"
  
'Fetching the queried data to the newly created Excel Workbook
  
  Set rst = New ADODB.Recordset
  rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText
  ActiveWorkbook.Sheets("Output").Range("D4").CopyFromRecordset rst

'Closing the ADODB connection
  
  rst.Close
  Set rst = Nothing
  Set cn = Nothing
End Sub

 

[Danny]

Hi,

Thank you for the code above, really appreciated!! This is far above my skill set when it comes to VBA.

When I copy that in, I'm getting a "compile error: User-defined type not defined"

Am i missing something here or being very stupid?

Thanks
Danny

 

[Sathish KV]

Hi,

Try the attached file.

You made need to change the ranges based on your requirement

 

[Danny]

Hi Sathish

I have tried your excel sheet, it errors before the ranges.







I wish I understood this better, as I cant begin to debug this.

Thank you

 

[Sathish KV]

Hi,

I belive you are selecting the cell 'N4' in sheet Interviews. Remove the hyperlink in the cell

You can also click cells N5, N6, etc...

 

[Danny]

GENIUS.

Thank you so much!!!

That has worked wonders, thank you so much for your time.

Regards
Danny