automatically updating other sheets when data entered based on certain data entered

[nmccool]

I am able to update other sheets based on certain data, but i am looking for it to do it automatically when data is entered; right now i have to manual push the data over to other sheets based on criteria.  

[#2]

I'm sure you are clear on what you are trying to do. It's hard, though, for others to read that description and be sure we're understanding. Could you post an example, with a bit more description of the situation, what happens with those "other sheets" and what isn't happening now, requiring manual info entry.

[#3]

@mathetes 

I am using SharePoint to update excel sheet 1 with info, and based on that info updated, i want to copy the line based on the code to the corresponding sheet.  So anything with "code 1" from sheet 1 will populate a row in  sheet  "01-Site Survey-Layout-Test-Demo".; attached

[#4]

Thanks for the sample. I'm going to defer to some of the folks here who have experience with Power Query, which, if not a VBA routine, is what I suspect you'll need. I don't work with either of those.

[#5]

thank you, I appreciate all the assistance

[#6]

@nmccool 

I created piece of code that works in a "simplified" workbook that I created based on your data. My aim was to keep it fairly simple, easy to follow and adjustable, even when you are not a VBA expert. I wouldn't even call myself one :)

Your example data shows  only codes 1 to 9, but you have 29 numbered sheets in your workbook (01 xxxxx to 29 xxxxxx) and only sheets 01 through 04 are formatted such that your sharepoint data fits to the 6 columns you wish to copy. Your workbook is clearly a test case (as its name suggests) and that makes it difficult to insert VBA code that will work in your live environment.

My code has all the target tables starting at A1 and I had to insert a dummy row at the top of the tables. Otherwise, I couldn't get the "xlDown" part of the code to work properly. And then I decided to store all sheet names in a separate table. Thought it was easier to include them in the code like that. A professional VBA programmer might not be impressed, but for me it works just fine :)

Selet the SheetNames tab, press the button. When the macro has finished it jumps to A1 in the "SharePoint" tab. You can review the result in each of the Sheets 1 to 9. I hope it helps you insert working VBA in your real workbook.

[#7]

@Riny_van_Eekelen  thank you, i will trying it this weekend! 

[#8]

@Riny_van_Eekelen This is JUST ABOUT perfect.  Is there anyway it will update automatically?  Without hitting the update Code Button?

[#9 · Accepted solution]

@nmccool 

You could also assign a key combination like Ctrl-U to the macro. Then you don't need a button. But something need to trigger the execution of the macro.

[#10]

I was trying to get Flow to update the workbook (as you seen SharePoint sheet), but Flow doesn't do that yet. Is there a way to have an excel spreadsheet update a macro enabled workbook (automatically)?

[#11]

@nmccool 

Well, you can certainly open a macro enabled workbook (.xlsm) using VBA. But, as far as I know, it does require user confirmation to either enable or disable the macro(s). 

Would it be an option to have the .xlsm file open to begin with and from there, have a macro pick-up your data from other file(s) and dump it into the "SharePoint" sheet, rather than having the other program/file push data into the .xlsm file?