Hope i can get some help here as my vba experience is extremely limited. I'm trying to run a macro from a spreadsheet that will go down a list of file names that i have entered in a worksheet where the macro resides and open those spreadsheets and rename the worksheets in each file according to a list of names that i have entered in the 10 columns next to the file name. It's easier to explain with the layout of my macro spreadsheet:
Sheet1
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 377px"><COL style="WIDTH: 60px"><COL style="WIDTH: 70px"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"><COL style="WIDTH: 62px"><COL style="WIDTH: 60px"><COL style="WIDTH: 61px"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"><COL style="WIDTH: 67px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet4</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet5</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet6</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet7</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet8</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet9</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Full Path</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>R:\Capital Markets\01B</TD><TD>01B PY</TD><TD>01B Collat</TD><TD>01B XIO</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>R:\Capital Markets\02A</TD><TD>02A PY</TD><TD>02A Collat</TD><TD>02A XIO</TD><TD>02A P</TD><TD>02A M10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>R:\Capital Markets\02C</TD><TD>02C PY</TD><TD>02C Collat</TD><TD>02C XIO</TD><TD>02C P</TD><TD>02C M7B</TD><TD>02C M8</TD><TD>02C M9B</TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
So i would like the macro to go down column D and open up the corresponding file and then rename the worksheets according to the corresponding cells in the columns to the right (columns E thru N). Then save and then close the file the macro just opened. As you can see from my data, sometimes there are only a few worksheets in each workbook and the number of worksheets per workbook varies.
Any help would be greatly appreciated.
Sheet1
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 377px"><COL style="WIDTH: 60px"><COL style="WIDTH: 70px"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"><COL style="WIDTH: 62px"><COL style="WIDTH: 60px"><COL style="WIDTH: 61px"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"><COL style="WIDTH: 67px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet4</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet5</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet6</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet7</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet8</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet9</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Full Path</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>R:\Capital Markets\01B</TD><TD>01B PY</TD><TD>01B Collat</TD><TD>01B XIO</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>R:\Capital Markets\02A</TD><TD>02A PY</TD><TD>02A Collat</TD><TD>02A XIO</TD><TD>02A P</TD><TD>02A M10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>R:\Capital Markets\02C</TD><TD>02C PY</TD><TD>02C Collat</TD><TD>02C XIO</TD><TD>02C P</TD><TD>02C M7B</TD><TD>02C M8</TD><TD>02C M9B</TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
So i would like the macro to go down column D and open up the corresponding file and then rename the worksheets according to the corresponding cells in the columns to the right (columns E thru N). Then save and then close the file the macro just opened. As you can see from my data, sometimes there are only a few worksheets in each workbook and the number of worksheets per workbook varies.
Any help would be greatly appreciated.