Matching Column Dates with Row Dates & Paste

What I would like to do is insert a macro in the To file that looks at the date row (Row 1) in the From file and pastes into the To file Column 1 at the appropriate row based on both docs having the same Month/Year. The row it's looking at will always be Row 22 on the From file. I've highlighted the data to be copied in the From file and where it should be placed in the To file - both in yellow.

Any ideas where to get started? I had thoughts of using a ExecuteExcel4Macro("match(" & CLng type code but can't seem to make it work.

The reason I thought VBA would work better is to have the ability to reuse the code on multiple To and From workbooks. My To and From workbooks are more of a stripped down version of what I would use the code in.

I tried putting the INDEX formula you provided into Col B on the To document and it just resulted in all 0s down to the bottom.
I also tried using the mylookup function you provided into Col B but it seems to have crashed Excel, highlighted the "Stop" in the code.

Prefer inserting formula(2nd) in this case.

Please Login or Register  to view this content.

jindon,
Much appreciated! In the testExcecutExcel4Macro() provided, I do have 2 questions.
1. Would I be able to change it so that it's not From.xlsx but perhaps something like From*.xlsx with a wildcard? I want to take from multiple sources but they would all start with the word From.
2. In addition, would I be able to change it so that the From workbook could be anywhere? If I'm not mistaken, it's assuming both workbooks are in the same location but in reality I'd rather it not work that way.

In that case, you need to actually select the workbook from the file explorer.

Problem is to find which ToWB needs to get data from which FromWB.

If you have clear logic to match up those 2 files, it could be done.

EDIT - I also just noticed that when I move it, it does open a window find the file but it does it repeatedly for every cell I want populated.

Of course, it is saying file is not found so asking the correct one.



I need to go off line now, so my next reply will be sometime tomorrow.

So, From workbook names are kind of specified to some extent, but how about the file folder location for each From workbooks?

It's broken down monthly and then by categories so it's not an easy find that way either.
September folder - so in a year, there would be 12 monthly folders.
1. Category A folder
2. Category B folder
3. Category C folder
4. Category D folder

Can you just show us how you manually find folder and From workbook?

Example of few ToWB(which is open as you mentioned esarlier) names and corresponding FromWB names and folder to search.

If you explain step by step, and if there is clear logic, it can be done.

I open the Forecaster Notes - Brock file and see that B1 has 10, so I locate the October folder on a shared drive.
The sheet name will match the contents of B2, so I would then find the "from" file, which would which would start NIT_C10-2023_1800. As mentioned in a previous post, all files will start with NIT_C, the 10 comes from it being October, -2023 of course is the year, then _1800 comes from the sheet name (also B2). So once that file is located, that's my From file.
I'm not looking for any macro assistance in finding the From file - there's no easy way to do that since it could be in one of many category subfolders, only to do a copy paste once I have the file located/open.

OK,

Is ToWB only Forecaster Notes - Brock.xlsb?‎ or are you also opening other ToWB?

If you are opening multiple ToWB and multiple FromWB, better create another workbook to hold the code, so that you can run the code from it.

Multiple although I was thinking I'd create one template of sorts and then just Save As so the code would be in all the To wbks.

You mean like Personal.xlsb or Add-In, but in this case you are opening multiple ToWB as well as multiple FromWB.
The code would then work from "ActiveWorkbook", and when "Activeworkbook" is not the one you want, problem.

So, my idea is to create a specific workbook to handle this and it is easier.

This is to show you what my idea is.

Open only relevant workbooks as well as this workbook and then run the code.

Please Login or Register  to view this content.

Can you just change Formula2 to Formula ?

That did the trick!

Try the code with multiple ToWBs with multiple ToWBs and see how it goes.

Please Login or Register  to view this content.

So using the NIT_C10-2023_1800 file as an example, I noticed it it no longer functions if NIT_C10-2023_1800 has anything extra in the file name, NIT_C10-2023_1800 would only be a prefix, not the complete file name. I thought this code put a wildcard in after the C. I tried renaming it NIT_C10-2023_1800-

Please Login or Register  to view this content.

Apologies if that wasn't clear.

That line is testing if workbook.name is not "GetData.xlsm" and doesn't begin with "NIT_C", so the code assumes the workbook is one of ToWB.

All my From wbks will stsrt with "NIT_C" though so I don't understand why it doesnt finish.

Can you upload 1 or 2 set(s) of FromWB and ToWB?

Forecaster - Notes Brock is the TO file.
NIT_C10-2023_1800 is the FROM file.
The macro I run is called GetShrink - sorry I forgot to delete the other 2.
The 2 things that make the macro fail are
1. Adding anything to the end of the FROM file name.
2. Adding any other sheets to the FROM file other than the current one.

When FromWB has multiple sheets, you need to specify sheet name or sheet index of the target worksheet.

Is this the last change that you make?
If something more, you MUST disclose everything in one post.

In the FromWB wbk, the sheet name will always be Monthly-Weekly Summary regardless of sheet number. I tend to prefer not using sheet index because if the sheets get shuffled around, it causes the macro to fail and then I get the blame and have to explain why it failed.

OK, so sheet name is common, that's good.
Try change "test" sub to below and Function GetWB should remain intact.
Jus in case if any FromWB doesn't have sheet named "Monthly-Weekly Summary", it tells you at the end.

Please Login or Register  to view this content.

So now nothing happens, even when Monthly-Weekly Summary is the only sheet and the wbk name doesn't have any extra characters.

Tested and it is working.

the "Test" macro or "GetShrink"?

GetShrink?

My code posted here was always "test".

I just wanted to be sure which macro you were running. I still can't seem to run Test. I'm baffled why it just does nothing when I run it.

If you upload your "GetData" workbook, I can test it here.