See how this works for you. I threw in some explanations, which may or may not help. The point, of course, is not a program that works for you, but one you can look at and say "oh, now I understand how to do that!".

Attached Files

x.xlsm (61.7 KB, 11 views)

@BobBridges Thank you! Yes, it works this time around! Looks like a lot more script was needed that I definitely will need to study in the morning bc it looks pretty complex!

I wanted you to know, I did have the preceding quote in there initially (I think on the 53 batch tab) but, when I saw it didn't give the desired result all the way through, I took it back out -- to demonstrate how -- if the wrong formula was copy/paste applied, then the Julian picked up would be inaccurate. I guess I should have left a sheet in tact showing it was there at one point =-(

Heading to bed and will study the details in the morning to walk through the steps and will let you know if I'm stuck on the acquiring wisdom part - which you're right -- LEARNING something from the pain is important and certainly very much appreciated.

Yeah, sorry about the complications. Part of the problem is that I've been doing this for a while, so some little extras that I used to skip ("too much trouble for this one little task") I eventually got into the habit of doing just because so often I later wished I had from the beginning. Like loading the table of formulae into a collection before applying them: When I was starting out no doubt I would have figure "Oh, there are only two or three different formulae, I may as well hard-code them into the program". Now, with experience, I agree with you that it's better to be flexible right from the start and leave room to add more as the need comes up—and now I just load the table into a table without asking myself whether I should, just because so often I need to in the end anyway.

I also am in the habit of specifying things like the workbook and worksheet:

Code:

Set owb = ThisWorkbook
Set ows = owb.ActiveSheet

Even though Excel often assumes the parents of the objects I refer to, and usually its defaults are what I wanted, every so often they aren't and I have to spend some minutes trying to figure out why the program isn't doing what I meant it to. Eventually I just got in the habit of writing it all out every time. You don't have to...until you've been programming for years :-).

And after a while I wrote some subroutines to save routine coding. I have a FetchWorksheet routine, for example, a function that I hand a workbook and a sheet name and it returns the worksheet—but if the worksheet doesn't exist (like if I forgot to create it, or spelled it wrong) the program prints a message instead of just bombing. An Exists function that returns True or False for a particular item in a collection. And so on. I tried not to burden you with that kind of junk; you can figure out for yourself when you want to write it. But the habits make my programs look more complicated than perhaps they need to be for your purposes.