Macro to Copy Values From Various Tabs to a Summary Tab
Macro to Copy Values From Various Tabs to a Summary Tab
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.
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.
- Attachments
-
- Need Macro.ods
- (24.85 KiB) Downloaded 171 times
LO 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
Re: Macro to Copy Values From Various Tabs to a Summary Tab
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Re: Macro to Copy Values From Various Tabs to a Summary Tab
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
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
- Attachments
-
- Need Macro indirectCF.ods
- (20.82 KiB) Downloaded 150 times
- MMove 1.0.6
- Extension for easy, exact positioning of shapes, pictures, controls, frames ...
- my current system
- Windows 10 AOO, LOLinux Mint AOO, LO
Re: Macro to Copy Values From Various Tabs to a Summary Tab
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
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!
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
All help is greatly appreciated!
LO 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
Re: Macro to Copy Values From Various Tabs to a Summary Tab
Hi,
it is not necessary to set it as an array formula in the CF-dialog, only in Cell H2.
it is not necessary to set it as an array formula in the CF-dialog, only in Cell H2.
- MMove 1.0.6
- Extension for easy, exact positioning of shapes, pictures, controls, frames ...
- my current system
- Windows 10 AOO, LOLinux Mint AOO, LO
Re: Macro to Copy Values From Various Tabs to a Summary Tab
Hallo
Try attached File
Sourcecode:
Try attached File
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 )
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: Macro to Copy Values From Various Tabs to a Summary Tab
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:
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
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: Macro to Copy Values From Various Tabs to a Summary Tab
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!
LO 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
Re: Macro to Copy Values From Various Tabs to a Summary Tab
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.
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.
LO 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
Re: Macro to Copy Values From Various Tabs to a Summary Tab
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. )
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. )
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)