Replacing multiple values across multiple consecutive workbooks in different directories

[Crobaro]

Hello All,

I'm trying to replace one cell with another across multiple workbooks in Excel 2016. The details are:

1. There are 10 files in one directory and correspond in order to 10 other files in another directory (i.e. the first file in each corresponds to the same client, but the workbooks have different names).
2. I would like to replace multiple cells from the first group of files to multiple cells in the corresponding file in the other directory. I.e cell A3 to cell B8, cell D4 to cell C2.
3. I would like to do this to all files in the directories at once and was hoping that because they are in 1 to 1 order within each directory it might help.
4. Lastly, some of the values I'm copying are actually formulas that I would like to convert to values when they are copies between files.

I hope I was clear. Thanks in advance for any help!

[JBeaucaire]

1) Open a blank workbook.
2) Turn on the macro recorder
3) Open one sample target workbook
4) Open the matching source workbook
5) Copy a cell from the source workbook and paste it into the proper place in the target
6) Save and close both workbooks.
7) Turn off the macro recorder.
8) Press Alt-F11 to open the VBEditor and locate the recorded macro.

Paste that macro here. From that, I can tweak that into a form you should be able to adjust as needed to do the same process on any number of cells for the matching workbooks.

[Crobaro]

Thanks for the help. Here is the requested code.

Please Login or Register  to view this content.

[JBeaucaire]

Sorry for the delay, I've been away.

A few things:

1) I notice the two folder paths are
"C:\Users\ccribari\Desktop\Exceltest\"
"C:\Users\ccribari\Desktop\Exceltest\testsub\"

Those will be easy to spot at the top of the macro I'm writing, you will edit those folder paths the ones you really need.

2) The 1-1 names between the books are not really 1-1
"Book1.xlsx"
Book1_sub.xlsx"

So, is the exact scenario we have to duplicate? Look for any filename in the FIRST path, then find the similar name in SECOND path with _Sub added into the filename?

3) Oddly, the macro above did not record you clicking on any specific cells. We'll use the ones from your post #1

4) The macro did not record you selecting any specific sheets within the workbooks, so we will use the FIRST sheet found in both of them, ok?

[Crobaro]

Perfect. Also, just a point to keep in mind, I would want to copy multiple cells. I.e. D1 in file1 to C3 file1_sub and at the same time A3 file1 to B7 file1_sub, etc. Not sure if I made that clear, but your assumptions above are what I'm looking for. Also, the files will be 1:1 match in their order in each folder, i.e. the first file in the main folder corresponds to the first file in the _sub folder.

Thanks!

[JBeaucaire]

Orders of file won't work. We must go by filenames.

Please confirm, whatever file we locate with any random name in path 1, we will add _sub to the name and that WILL be the source file from path 2?


Cat.xlsx > cat_sub.xlsx
Book22.xls > Book22_sub.xls
Data.xlsb > Data_sub.xlsb

[Crobaro]

Thanks for this. Quick question, how do I define the cells whose data will be put in to other cells from book1 to book1_sub (i.e. C3 in book1 to D4 in book1_sub). As well, does this go on down the line copying defined cell data from one workbook to another for book2 to book2_sub, book3 to book3_sub, etc. ?

[Crobaro]

"Cat.xlsx > cat_sub.xlsx
Book22.xls > Book22_sub.xls
Data.xlsb > Data_sub.xlsb"

That would work fine as I can change the naming conventions as needed, thanks!

[JBeaucaire]

OK, if you confirm that will work, then I will put it together.

The code will be pretty obvious what it's doing and where you will edit the mapping of source cells > target cells.

[Crobaro]

Confirmed and looking forward to seeing it, thanks!

[JBeaucaire]

This is what I have put together. It will fall to you to test it....

Read it over an let me know if any of it is unclear before you try it out.

Please Login or Register  to view this content.

[Crobaro]

May be a silly question, this works for the first workbook. How do I make it do all workbooks automatically in these folders?

Thanks again!

[JBeaucaire]

It's a looping macro, it should work for every pair of matching workbooks.

fPATH1 = target workbooks
fPATH2 = source workbooks (with _sub added to name that matches to target workbooks)


The only concern I have is that the fNAME1 code does see more than one file. So use F8 to step through this macro one line at a time. When you get to this line of code

Please Login or Register  to view this content.

pause for a moment once you pass it, hover your mouse over the fNAME1 to see what the filename it found is... is that different each time through the loop, or does it keep finding the same file over and over?

[Crobaro]

fName1 =Book1.xlsx, then on second loop does not see any more workbooks (there are 6 in each folder per the naming conventions). The second time through the loop, fname = "".

Does that help?

Thanks!

[JBeaucaire]

Ok, I think it is the second use of the DIR() function to test wb2's existence is messing up the natural behavior of a single instance of DIR(). So I've removed the second instance. Try this:

Please Login or Register  to view this content.

[Crobaro]

I think that little tear of joy is on its way. This is perfect, thank you JBeaucaire!

[JBeaucaire]

Awesome! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.