Macro to Copy Values From Various Tabs to a Summary Tab

[crusader]

I am an experienced spreadsheet user with zero macro/programming knowledge. I am requesting assistance with a macro that will do what I have the various formulas and conditional formatting doing. My attempts to record macros did not work out. The reason I am looking for a macro is I believe the spreadsheet will respond to changes much faster (if this is not the case, please let me know). I would like the macro to make the changes as soon a cell value is changed (just like the formulas do right now). The attachment is an abbreviated version of a spreadsheet I have.

What I need is for data in column A of all but the first sheet to be copied to the first sheet ("Summary") - in the appropriate column. The values in column A in any sheet will have no duplicates. I also need the macro to bold, center, and change the font to blue all values that have duplicates in the Summary sheet. The example presents this better than I can explain.

The software I am using is accurately reflected in my signature line. I will be happy to clarify any questions. All help will be greatly appreciated.

[JeJe]

viewtopic.php?f=45&t=21541

[F3K Total]

Hello,
i didn't try to write a macro, but modified the conditional formatting to only analyse filled cells, see formula in cell H2. Maybe this version is faster, please try.
R

[crusader]

Thank you for link, JeJe. Amending the code to my needs, I came up with the following (and it works); however, questions remain:

1. Entering rest of the sheets in the code below (I need no more than 250 rows from column A copied - starting with row 2)
2. Assigning values copied from rest of the sheets to columns B, C, D, etc., (one column per sheet) in the Summary sheet
3. Coding Conditional Formatting

Code: Select all

Sub Main
       Doc = ThisComponent
       Sheets = Doc.Sheets()
       SheetCopy = Sheets.getByName("Sun")
       SheetPaste = Sheets.getByName("Summary")
       CopyRange = SheetCopy.getCellRangeByName("A2:A250")
       PasteRange = SheetPaste.getCellRangeByName("A2:A250")
       PasteRange.DataArray = CopyRange.DataArray
End Sub

F3K Total, thank you for the modified Conditional Formatting formula; I was unable to get it to work (I suspect it was because I was unable to set it as an array formula in Conditional Formatting dialogue). I tried entering the braces manually, but that did not work (understandably). I will continue working on it: the idea of skipping blank rows promotes efficient resource utilization - and is likely to speed up operations.

All help is greatly appreciated!

[F3K Total]

Hi,
it is not necessary to set it as an array formula in the CF-dialog, only in Cell H2.

[karolus]

Hallo

Try attached File

with_python.ods

(18.65 KiB) Downloaded 169 times

Sourcecode:

Code: Select all

def summary(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sheets = doc.Sheets
    summary = sheets.getByName( "Summary" )
    sumRange = summary.getCellRangeByPosition
    header = summary.getCellRangeByName( "A1:G1" ).DataArray
    header = header[0]
    for index, name in enumerate( header ):
        sourceSheet = sheets.getByName(name)
        source = sourceSheet.getCellRangeByPosition(0, 1, 0, 250)
        source = source.DataArray    
        sumRange(index, 1, index, 250).setDataArray( source )
 

[karolus]

Hallo

with Libreoffice5.1 comes an renewed PyUno-Interface Details which allows in many cases more compact syntax,
for example the code above can be replaced by:

Code: Select all

def summary(*_):
    sheets = XSCRIPTCONTEXT.getDocument().Sheets
    summary = sheets.Summary # ObjectAccess only for SheetNames without whitespace ect.
    header = summary[0, :7].DataArray[0]
    for i, name in enumerate( header ):
        data = sheets[name][1:250,0].DataArray 
        summary[1:250, i].DataArray = data

[crusader]

Thank you, F3K Total and Karolus. Unfortunately, I will not be able to work on the project for a week or so; however, I when I get to it, I will provide feedback on your suggestions. Your support is greatly appreciated!

[crusader]

Got a chance to squeeze some time for this project. My feedback:

F3K Total, your formula works wonderfully.

Karolus, I was unable to see the code in the attachment you sent. It said "read only," but I was unable to even see it - even after I saved the file to my computer (I used the Python option in Macros, but this did not work either). Both the codes you also shared did not work. LO pointed to the asterisk (*) as a problem. Needless to say, I have no idea what this all means.

Thank you for the support.

[karolus]

Hallo
I'm pretty sure that both Code-snippets "does work" in this Document … because:

- I wrote both based on your needmakro.ods
- Your "Environ" looks very the same as mine ( LO5.3 on LinuxMint 17.3 ) but its the LO out of the Mint-Repositories.

( I've used this optional ArgumentPattern *_ in the Signature to pretend Fails on calling via Buttons, Events etc. )