I'm trying to fill specific cells in multiple Excel sheets with data from a table.
Here is an example of what I'm trying to do.
I am aware that you could autofill every single sheet separately with the data from the first sheet. But since there are a lot of sheets I was wondering if there was a simpler way to do this.
There's a couple of ways forward here, but they rely upon your layout matching from page to page. And that the formula in the result cell is the same from page to page.
For instance, say you have the single lookup cell set you show in the example that has to go on every page. You could either select all the workSHEET tabs for all the pages and they will edit as a group so when you build that lookup cell set on one of them, the same things are being entered in the same places on all of them, or you could build one sheet, then copy that sheet as needed to have all the pages you need to do your work.
If you are starting fresh, the latter route is surely easier. If you have existing sheets and material and need to add these lookup blocks, the former is the way to go.
It's more complicated if they need to be on each sheet, but in slightly different places (so not, say, B10:C11, but maybe some belong in F23:G11). In that case, you can find an out of the way place on the sheets, follow the former approach above (select all tabs, etc.), and build the block. Then go to each sheet and select, then drag the block cells to their needed locations on the different pages. If those differences are limited, maybe just three different sets of addresses, you could mix that up some to do the select tabs and fill all of the first set, then the next group and finally the last. Exact needs would dictate but follow the idea.
Or, just build it on one, then Copy the lookup block to the Clipboard, and go to each page Pasting the block as you go. If you use absolute addressing for the lookup ranges (lookup value, lookup result column or lookup result range), and relative addressing for anything else, that would work nicely too.
Another approach would be to build the block, then use VBA's Immediate Window to copy and paste. Building the range to paste too would be easy using standard string building techniques so that'd do it in a couple little steps rather than mousing about amongst the sheets, and since it would be a one time use command in the Immediate Window, there's no macro, no saving as a macro file, nothing like that at all.
But if the sheets will be essentially the same, looking up an entry on each's sheet, but in a data range on a "master page", AND you are building it from scratch, create the master sheet, then one page that has the lookup/s and anything else a page needs, then just copy the sheet as many times as needed.
