[Solved] A Save and Refresh Range Macro

[PickmansModel]

Just spent 2 hours on the forum looking for this ...
Is there a way to write a macro to "save" the spreadsheet and then "refresh range" so after I update my data, I can just click the button and have the range show as updated?

[patel]

can you explain better and attach a sample ?

[PickmansModel]

So I have a spreadsheet/database set up to get selected (those rows with an "x" in the prepared and known columns) information from sheet1 and put it on sheet2... that works fine.

It works fine, but when I change the data on sheet1 (like if I "select" different or more rows) I need to save the spreadsheet (CTRL+S) and then select my range of data on sheet2 and then Data>Refresh Range.

I know you can make buttons using Form Controls and assign that button a macro. I'd like to write a macro that saves (CTRL+S) and then Refresh Range, so when I update my data on sheet1, all I need to do I click the button to update sheet2.

Savvy?

[FJCC]

I think this macro will do the job. ThisComponent refers to the document that calls the macro

Code: Select all

ThisComponent.store()
oRange = ThisComponent.DatabaseRanges.getByName("Import1")
oRange.refresh()

[PickmansModel]

I think this macro will do the job. ThisComponent refers to the document that calls the macro

Code: Select all

ThisComponent.store()
oRange = ThisComponent.DatabaseRanges.getByName("Import1")
oRange.refresh()

Nothing happened ... I copied your code then Tools>Macros>Organize Macros>Basic>New
Then I pasted your code between Sub Main and End Sub... Then I added some "X"s on sheet1 and ran the macro. My range did not refresh. Then I saved first, and ran the macro again... still nothing.

[Villeroy]

Works for me. Works for me. I stored the macro in the document so ThisComponent always refers to the intended document.

[PickmansModel]

I'm a moron ... I had the Macro Security set too high. Works perfectly. Thanks guys!!!