SOLVED

Create a new table selecting info from an existing table in Excel vba

Copper Contributor

Hi everyone!

I am trying to make a nice table (sheet 2) out of different information about a company from a bigger table (sheet1). The info is in different language and has more sections than needed to create the new table (intended to create a nice CV). 

The goal is to create a VBA that automatized this to gain time. 
I am so lost with this because I do not know anything about VBA, so I was hoping someone could help/guide me. 

 

I put an example of what I would like to create. So basically all the different projects are on sheet 1 with all the different sections (=columns) per project. On sheet 2, there is the table I would like to be able to create with a VBA.

Let me know if you are able to help me, I would be so grateful!!!

 

Cheers,

 

Déborah

13 Replies

@dfhidromod 

See the attached version (now a .xlsm workbook).

Run the macro ShowForm.

@dfhidromod  It is possible to do much of this with worksheet formulas.  I have use Excel 365 to put the entire calculation into a single formula in cell F1.

= LET(
\0, "Generate indices",
    N, COUNTIF(Project[Working Area],Criterion),
    k, SEQUENCE(8*N,1,0),
    recordOffset, QUOTIENT(k,8),
    fieldOffset, MOD(k,8),
\1, "Filter fields and records to extract relevant data",
    headers, FILTER(Project[#Headers],columnFilter),
    selectedFields, FILTER(Project, columnFilter),
    filteredKey,  FILTER(Project[Projeto], Project[Working Area]=Criterion),
    filteredTable, FILTER(selectedFields, Project[Working Area]=Criterion),
\2, "Assemble report recombining project key and selected data",
    reportKey, INDEX(filteredKey, 1+recordOffset),
    reportHeader, IF(fieldOffset, INDEX(headers, 1+fieldOffset),  reportKey),
    reportData, INDEX(filteredTable, 1+recordOffset, 1+fieldOffset),
    report, CHOOSE({1,2}, reportHeader, reportData),
    report )

image.png

@Peter Bartholomew 

Dear Peter,

 

thanks a lot for your help! I see in your file that you got the exact result I wanted. 

However, when I try to redo it, it does not work.

So the pink table should select and filter the projects according to their working area, right?

So for this, you used the formula "= SORT(UNIQUE(Project[Working Area]))" but when I do it, it does not want to link both sheets: the one you named DataTable and Report. 

 

And then on the left side of that same worksheet "Report", you have "N=" in one cell and then "=COUNTIF(Project[Working Area],Criterion)" which does not work either when I try it on my own file. 

 

Do you think you know what is the problem here? and if yes, would you mind explaining it to me?

 

Thanks a lot for your file again, when it will work, it will simplify and automatize so much my work!! :)

 

Regards

@dfhidromod 

Is your table named 'Project' or is it named as default 'Table1' (or the equivalent in Portuguese).

For the filtering to work you need to be using Excel 365.  Otherwise the formulas get very messy.

@Hans Vogelaar 

Dear Hans,

 

what a script! Thanks a lot!

However there are things I do not understand and I am having a hard time to reproduce the code on the real document.

In these lines:

<For r = 3 To m
For i = 1 To n
If v(r, 13) = arrSelected(i) Then> 

 

  1. Why did you choose 3 for r =3?
  2. and also, the v(r,13)= arrSelected(i), what does it mean? why 13?
  3. What do "A" and "B" stand for? because there is nothing in Columns A and B, so I am guessing it is not referring to the columns. But what is it then?

I know you've already helped immensly, and I am novice in this language, but would appreciate if you took the time to answer me again :)

 

Regards,

 

Déborah

@dfhidromod 

  1. Because the data on Sheet1 start on row 3 (row 1 is hidden and row 2 contains headers).
  2. v is an array that contains the values of the cells in columns A to Y, row 1 to m (the last used row). v(r, 13) is the array element in row r and column 13. Column number 13 corresponds to column M, the Working Area column.
  3. A and B are the columns on the new sheet that are filled by the code.

@Hans Vogelaar 

 

Okay thanks for the explanations!

 

And one more thing, in this line <w.Range("B" & t).Value = v(r, 4 + s)> what does the 4+ s stands for?

 

And at the end in this line: <w.Range("A1:B1" & t).Borders....etc> the A1 and B1 correspond to the new sheet too? I am having troubles identifying where you set that the code opens a new sheet for these variables.

 

Cheers!

@dfhidromod 

The line

    Set w = Worksheets.Add(After:=Worksheets(Worksheets.Count))

creates a new worksheet and assigns it to the variable w, so in the rest of the code, we can use w to refer to the new sheet.

 

The variable s contains a number representing the selected language: 1 for Portuguese, 2 for English etc.

I see now that 4 + s should have been 3 + s. This is the column number containing the titles in that language: column 4 (D) for Portuguese, 5 (E) for English etc.

I will attach a corrected workbook.

 

In w.Range("A1:B" & t).Borders, w is the new sheet, so this line refers to columns A and B on the new sheet.

@Hans Vogelaar 

Okay! Starting to understand better! This code is really impressive and cool.

 

I tried it on my new worksheet (because I had to add things) (and adapting the code to it), but when I select the languages, nothing happens. I don't even get an error message. There is just nothing.

 

In the file attached to this message, there are the headers I am using and their positions in the worksheet. I thought I changed everything I needed to make it work, but does not seem to give me results. 

 

Do you think you could have a look at the changes I made to your code and identify what I've missed?

I marked all the changes in green with this " ' "

 

Cheers!

best response confirmed by dfhidromod (Copper Contributor)
Solution

@dfhidromod 

The major problem: you changed cmdCancel_Click to cmdCancel_Click3 and cmdOK_Click to cmdOK_Click3. That won't work: Click is the name of the event, you can't just change that.

Other problems: the headers are now in row 1 instead of row 2, and your column offsets for the context and activity were off by 1.

See the attached version.

@Hans Vogelaar 

okay, I see! thanks for the explanation!

 

Another last thing, do you know how to define the width of the 2nd column in the new sheet created?

Because, i get super wide columns and the text does not fit in it

 

Attached, there is a picture illustrating better what I'm trying to explain.

 

All the best,

 

Déborah

 

@dfhidromod 

Hopefully, this works better.

1 best response

Accepted Solutions
best response confirmed by dfhidromod (Copper Contributor)
Solution

@dfhidromod 

The major problem: you changed cmdCancel_Click to cmdCancel_Click3 and cmdOK_Click to cmdOK_Click3. That won't work: Click is the name of the event, you can't just change that.

Other problems: the headers are now in row 1 instead of row 2, and your column offsets for the context and activity were off by 1.

See the attached version.

View solution in original post