Merge and Rename Worksheets Based on Matching Cell Values

[lager1001]

Good morning,

I have excel files in directories that I need to rename based on the value in specific cells in each worksheet and need help with a macro code to achieve this. I would like the macro to loop through the folders and rename each file based on the value it finds in the indicated cell. I've been searching through codes but haven't found anything that quite does what I need it to.

I'd like to point the macro to the directory either via navigating to it, or by hard coding the directory path within the macro.

I will be doing this for 3 different directories then merging the newly created files from each directory into 1 workbook based on the newly matched file names. The file from Directory 1 becomes Worksheet 1 of the newly merged workbook, the file from Directory 2 becomes Worksheet 2, etc.

See below table for an example:

456789	PH12345	678678	PH12345	232214	PH12345	PH12345_A-Class_Denver
124352	PH23523	786782	PH23523	654665	PH23523	PH23523_A-Class_Chicago
465634	PH34234	544253	PH34234	425345	PH34234	PH34234_C-Class_Philadelphia
653634	PH25533	123213	PH25533	856658	PH25533	PH25533_B-Class_Amarillo
736343	PH87897	924221	PH87897	324234	PH87897	PH87897_B-Class_Tucson

<tbody>
[TD="width: 152, bgcolor: #B2B2B2"] Directory 1 File Names [/TD]
[TD="width: 144, bgcolor: #B2B2B2"] D1 New File Name [/TD]
[TD="width: 150, bgcolor: #B2B2B2"] Directory 2 File Names [/TD]
[TD="width: 141, bgcolor: #B2B2B2"] D2 New File Name [/TD]
[TD="width: 149, bgcolor: #B2B2B2"] Directory 3 File Names [/TD]
[TD="width: 145, bgcolor: #B2B2B2"] D3 New File Name [/TD]
[TD="width: 533, bgcolor: #B2B2B2"] Merged File Names [/TD]

[TD="bgcolor: #B2B2B2"][/TD]
[TD="bgcolor: #B2B2B2"][/TD]
[TD="bgcolor: #B2B2B2"] Rename as Cell I12 [/TD]
[TD="bgcolor: #B2B2B2"][/TD]
[TD="bgcolor: #B2B2B2"] Rename as Cell H11 [/TD]
[TD="bgcolor: #B2B2B2"][/TD]
[TD="bgcolor: #B2B2B2"] Rename as Cell H8 [/TD]
[TD="bgcolor: #B2B2B2"] Based on Concatenated Values from Cells I12, D4, E2 from Directory 1 Worksheet [/TD]

[TD="bgcolor: #B2B2B2"] File Names [/TD]

[TD="bgcolor: #B2B2B2"][/TD]

[TD="bgcolor: #B2B2B2"][/TD]

[TD="bgcolor: #B2B2B2"][/TD]

[TD="bgcolor: #B2B2B2"][/TD]

[TD="bgcolor: #B2B2B2"][/TD]
[TD="bgcolor: #B2B2B2"][/TD]
[TD="bgcolor: #B2B2B2"]Becomes Worksheet1[/TD]
[TD="bgcolor: #B2B2B2"][/TD]
[TD="bgcolor: #B2B2B2"]Becomes Worksheet 2[/TD]
[TD="bgcolor: #B2B2B2"][/TD]
[TD="bgcolor: #B2B2B2"]Becomes Worksheet 3[/TD]
[TD="bgcolor: #B2B2B2"][/TD]

</tbody>

Any guidance is appreciated!

 

[sijpie]

Re: HELP - Merge and Rename Worksheets Based on Matching Cell Values

Why rename first, then merge? Why not merge straight away using the correct names for the sheets?