I am new to Libre Calc. I need guidance to create a macro to create and populate the cells of a new worksheet when the value in another worksheet is true.
Trying to create an absentee mailing list from a master list. Need to populate Name (col 2), address (col3), state (col 4) and zip (col 5) from sheet Master.
create worksheet from value in cell
This title doesn’t match what I understand from your detailed description, which I understand as “Fill cells of a worksheet based on search in another (“Master”) worksheet” (Creating a worksheet based on cell content would require a macro).
Now I’m completely confused (used conjunctive would in parenthesis of my comment) … From My understanding you want to have 2 worksheets:
- Sheet 1 (“Master”) containing all data
- Sheet 2 (“Absentee Mailling”) containing an excerpt from Sheet 1 (“Master”) based on … (what information from where?)
Even if you now talk about creating a macro it still keeps completey unclear where to take the selection-critereria (!!!) to create Sheet 2 (“Absentee Mailling”) from.
Excel speak “worksheet”
Calc speak: “spreadsheet” (or simply “sheet”)
Newspeak “populate”
Suggested (by @ Lupp): “fill”
Inspect the attached example and tell what’s wrong.
ask285550filterFromMasterToExtractsheet_1.ods
Now I’m completely confused … (@anon73440385)
By what? By my comment?
Well, I assumed a task like “Get an extract from one sheet of a spreadsheet document into a nother one”.
This can be done by a Standard Filter or by simple formulas (array-evaluation probably).
In the second case “compacting” by formulas is a bit more demanding.
OK It was the macro thing. I hadn’t read the edited version yet.
Again and again the same mess. In addition it os still unclear if actually a next sheet in the existing document is the goal or a new spreadsheet document comtaining just one sheet with the results. In the second case we wouldn’t know where to find the name …
“Guidance to create a mmacro”? Mostly the simplest “guidance” is to write it.
By my comment?
Absolutely not, your comment wasn’t visible to me, while writing this. Just tried to figure out what OP really requires without being forced to make assumption and indeed the simple solution could be a Filter using a helpers column absent in Master sheet (but again forced to use could).
Yes. I assumed the simplest way to explore the questioners actual needs was to give a solution and to wait for the criticism.
If you want to automate anything for LibreOffice documents by user code, you need to study the fundamentals.
Ordinary user functions enhancing Calc a bit are often rather simple, and may be written by a beginner only knowing a few basics about Basic.
User code actually working with te document is completely different.
Study the fundamental parts about the LibreOffice (also OpenOffice.org or Apache OpenOffice) API in the famous documents by Andrew Pitonyak (first table below the introduction there, mainly “Macros Explained” and “Macro Document”), and use the texts then probably selective.
For an example how to do what you asked for you may study the code contained in the attachment. I would expect you find it too steep an entrance for your learning. You will also see that there cannot be “guidance” enabling you to solve the task yourself starting at about zero level. Also note please, that my “style” is somehow different, and doesn’t regard some of the widely used, but never specified conventions (concerning the naming of variables e.g.). I’m also a fan of dense overview and less of sparse lines.
A wholesome effect of the attempt to get a macro for your task may be the perception that it often is atually preferrable to look for solutions by standard means of spreadsheets. The inflationary use of macros known from the Excel “community” isn’t the best idea, probably.
That is exactly what I needed. Thank you so much.
Correction: Line 17 of the contained code should read .Field = sourceColumn
Otherwise the the definition of the constant sourceColumn is ignored by the filter settings.