Forum Discussion
Create a new table selecting info from an existing table in Excel vba
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.
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>
- Why did you choose 3 for r =3?
- and also, the v(r,13)= arrSelected(i), what does it mean? why 13?
- 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
- Because the data on Sheet1 start on row 3 (row 1 is hidden and row 2 contains headers).
- 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.
- A and B are the columns on the new sheet that are filled by the code.
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!
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.