Nov 14 2019 11:12 AM
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.
Nov 14 2019 11:35 AM
Nov 14 2019 12:29 PM
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
Nov 14 2019 01:59 PM
Nov 14 2019 02:03 PM
Nov 15 2019 03:32 AM
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.
Nov 15 2019 06:58 AM
@Riny_van_Eekelen thank you, i will trying it this weekend!
Nov 15 2019 11:53 AM
@Riny_van_Eekelen This is JUST ABOUT perfect. Is there anyway it will update automatically? Without hitting the update Code Button?
Nov 15 2019 12:31 PM
SolutionYou 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.
Nov 18 2019 01:53 PM
Nov 18 2019 11:17 PM
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?
Nov 15 2019 12:31 PM
SolutionYou 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.