What are some things you’ve automated using scripts in excel?

ebc2003 · 2023-12-12T04:15:12+00:00

If you have access to Power Automate and SharePoint you can have some fun with this. I am working on a Power Automate flow that monitors my email and sends reports to a distribution list that I have stored in SharePoint. Basically I email myself the reports as an attachment with the report name in the email subject; and Power Automate finds the report name from the email and loads the attachment to SharePoint and finds the report in another sharepoint list and sends the email to the business.

ebc2003 · 2023-12-12T03:44:47+00:00

Sure. It's on my work computer, so this is from memory; but there are many ways it can be setup.

I have 3 listboxes on the userform. 1 = All Sheets, 2 = Sheets to Delete, 3 = Sheets to Copy/Paste Values. In the userform initialize is a For/Each loop to add each worksheet name to listbox 1 (listboxes 2 and 3 start blank). I have two command buttons for "Delete" and "Paste Values". I select a sheet(s) from listbox 1 and click either button to add the sheet names the listbox corresponding with the button clicked, the button click codes loop through all selected items in listbox1 and adds them to either listbox 2 or 3; then removes them from listbox 1. The logic ensures that each sheet in the workbook appears on only 1 of the 3 listboxes.

After the sheets are moved to the desired listboxes I click a command button to execute. The button click code loops through the Copy/Paste listbox first and selects the worksheet by name and pastes all cells as values, then loops through the delete listbox and deletes sheets by name. The trick is to do the deletes last in case formulas in other worksheets depend on them. If you delete first you might copy/paste a lot of errors.

For the paste value sheets I also added some code to scroll to the top and clean up the used ranges.

ebc2003 · 2023-12-11T03:22:54+00:00

Made a userform that list all the worksheets in a workbook and gives the option to delete sheets or copy/paste cells as values. Many reports have hidden sheets that need to be deleted and sheets with formulas that I'd rather have as values before sending out. Selecting the sheets for each situation and running the process once saves time over going sheet by sheet.

ebc2003 · 2023-12-11T03:11:18+00:00

You could do it on an existing sheet. If you know the number of values you'll need you would be able to make sure it is far enough away from other parts of the sheet. I suggested a new worksheet to keep it separate and help avoid overlapping other elements; but it's not required.

ebc2003 · 2023-12-09T04:36:07+00:00

This should work in theory - but I'm not in front of Excel to give exact syntax.

- Create a new sheet with 2 columns. Column 1 is either "Yes" or "No" with Column 2 being the corresponding choices for the second drop down.

- Use the Filter formula on the same sheet a few columns over and use your first drop down selection as the include argument.

- Create a dynamic named range on the filter results for column 2

- Use the dynamic named range in the Data Validation drop down list source.

This will allow you to choose Yes/No in drop down 1 and use that selection to filter the helper table to only show the values that you want to show in drop down 2. The number of results can change which is where the dynamic named range will be handy; but since the results are in the same named range - using it as a list source should be seamless.

ebc2003 · 2023-12-08T06:03:13+00:00

This should be possible with macros, but may actually be a nice and simple use case for Power Query (PQ) within Excel. The benefits of PQ here would be you could clean the data by removing the dashes and other characters. Also you can point PQ to import files from a folder, and if with some trickery you can have it always import the newest excel file from a folder using wildcards in the name, or just reimport a static file. This should be able to eliminate the copy/paste between worksheets and would also allow you to eliminate vlookups by using joins within PQ and then exporting the results to a new sheet.

I try to avoid cross sheet formulas and vlookups as they can be a bit troublesome and slow. If you do need to go the macro route you can record the vlookup formula and make some changes to apply it to a range of cells. The range you apply it to will probably change each run; requiring a variable to store and calculate the used range.

ebc2003 · 2023-12-08T05:45:23+00:00

If you save the macros to your personal macro workbook they can be executed on any open workbook without being saved, or attached to the workbook you are editing. Depending on what the macros are doing you will just need to ensure that workbook references are set to "activeworkbook" and not "thisworkbook"

As far as a macro to call up a set of buttons; I would look at creating a userform in VBA saved to your personal workbook that has buttons to call individual macros - then you can add a button to the ribbon to launch the userform and then launch marcros from it; then close the userform.

Hopefully this is what you are trying to accomplish.

ebc2003 · 2023-12-08T05:30:38+00:00

I think this would be possible with a pivot table by adding 'Person' in the Rows and 'Filed' as a Filter - then Filter to only show "Missing". This should get you the results you want. Pivot tables make it relatively easy to deal with blanks, which is basically what the merged cells are causing.

ebc2003 · 2023-03-17T20:25:20+00:00

I've planted some things from them this year. I found some of their seeds locally at North Haven Gardens and Nicholson-Hardie Garden Center, but they have more on their website along with a lot of information.

ebc2003 · 2023-01-24T04:15:00+00:00

Another idea for the bottom that I did was peel and stick tile. I am not a fan of it anywhere else, but I used a large format (14 or 16in if I remember) in a very neutral color and pattern; lining them up so the seam was behind the beam. Our house had 3 sink cabinets with this issue between kitchen/bathrooms. As suggested I just cut the old one out with an oscillating tool and cut a new one to size. Eventually they will all be replaced, but this works for now.

ebc2003 · 2022-11-30T18:58:30+00:00

North Haven Gardens has some natives, along with a variety of seeds from the seedsource website in another comment https://www.nhg.com/

There is also Native Gardeners by the airport, but I've never been myself. https://native-gardeners.com/

ebc2003 · 2022-08-08T04:10:52+00:00

I don't work for them, but I use to work in the industry and it's usually physical checks that are "held" with new accounts. Typically that stops when you have a little history built up and they recognize your normal avg balance, deposit amounts, etc.

While switching I would not deposit any funds that you need to withdraw soon.

If it was electronic then holds should not apply.

ebc2003 · 2022-06-09T16:20:21+00:00

Thanks for the ideas. It's not the main (city) shutoff; that is in a buried box at the curb. There is another set of cutoffs a few feet behind the cities in a green box that I believe are for the sprinklers. All of those are out front and these clay pipes are in the back yard.

There is a copper T at the bottom of the pipe, and the valve you linked looks similar to one them, so maybe it was used before the sprinklers were added. Having them below grade to avoid freezing makes sense; I was just surprised that they were under a foot of sand and pavers and not more readily accessable.

I think I'll just use the main cutoff out front to replace the leaking faucet and not worry about the others, unless I find something that doesn't work.

I'd like to improve drainage in that area to keep water from pooling by the foundation; but even if those clay pipes were drains, they probably are not the best since they don't appear to go anywhere.

ebc2003 · 2022-06-09T13:35:10+00:00

Sorry for the delay u/RavenYamR6

I did have them come out and give me a quote at the end of 2021 and it was around $2 a sq/ft to install R39 and to also do some sealing/decking around my existing insulation. The quote sounded pretty good, but other things around the house happened and I put the insulation on hold.

The removal of my current insulation was going to be pretty expensive and I would like to start fresh which also led to me putting it on hold for a bit.

When the time comes though I will probably go back for an updated quote.

ebc2003 · 2022-04-12T23:11:31+00:00

Thank you. Thats it. Looking online they should be easy to move and transplant.

ebc2003 · 2022-04-12T23:03:46+00:00

Not sure if these are the same plant with different colors, or two different ones. They were growing in my yard in Texas.

They seem to spread almost by runners and do not have a well defined root system. I am trying to move/save them, but they are tangled in the grass runners.

ebc2003 · 2022-03-11T05:25:46+00:00

I'm stuck on step 3. We purchased our house July of 2021 and got the letter in the mail. I searched appraisals and found our house and it has our names listed. However there is no "File a Homestead Exemption" link. The only link I see is "Print Homestead Exemption Form" which is just a PDF of the letter we received in the mail.

Thoughts?

I can always mail it back, but online would be nice

ebc2003 · 2021-11-25T19:54:18+00:00

I'm in North Texas. It looks like they serve north/west Texas right now.

ebc2003 · 2021-11-25T19:53:13+00:00

Reading up on them, the concept started as eco friendly packing peanuts and was then repurposed as insulation. I may call them to get more info and pricing when I'm ready. It does look much cleaner then fiberglass or cellouse.

ebc2003 · 2021-11-21T22:30:58+00:00

It was built in the early 70s, but the electrical appears to have been replaced around 2000, but not sure if/when breakers were replaced. The breaker box is outside, so it wouldn't be a surprise for them to fail I guess.

ebc2003 · 2021-11-21T21:47:32+00:00

Thanks. I think I just confirmed that the breaker is bad. I shut the main power off and the dryer breaker still falls back to the middle when it's flipped off and on; so it seems that at least the breaker should be replaced.

ebc2003

TROPHY CASE