Loop to my Loop challenge

[joeshu26]

Hello,

I am looking for help on a code to loop through sheets of data (the sheet names are dates listed in the column below labeled "Sheet Name".) I would like loop through each sheet that is a date and paste cell "D8" into column E in my table below for each corresponding date that has a Life of 5 in column D. For Each Date that has a Life of 7 I would like data from the sheets to be pasted in from cell "E8" into column E of the table below.

I would like this process above repeated above for column F of the table below, which would simply paste in data from "D9" and "E9" for each resepective sheet date and Life of 5 and 7. Any takers on this challenge would be appreciated!

	A	B	C	D	E	F
1					AA	BB
2	Path	Workbook	Sheet Name	Life
3	G:\Doc Files\Spread\	Spread2011.xls	1/7/2011	5
4	G:\Doc Files\Spread\	Spread2011.xls	1/14/2011	5
5	G:\Doc Files\Spread\	Spread2011.xls	1/21/2011	5
6	G:\Doc Files\Spread\	Spread2011.xls	1/28/2011	5
7	G:\Doc Files\Spread\	Spread2011.xls	1/7/2011	7
8	G:\Doc Files\Spread\	Spread2011.xls	1/14/2011	7

<TBODY>
</TBODY>

 

[Russell Hauf]

First of all, can you have sheet names with slashes in them? I'll assume that you have valid sheet names in column C, and that they exist.

I'd probably do something like this. Please note the following:

• I assumed that there is more than one workbook (column B)
• see above about the sheet names
• I try to never use "ActiveSheet," but without knowing more about your project...
• this was not tested

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> DateLoopMrXL()<br>    <SPAN style="color:#00007F">Dim</SPAN> lngRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> strWbkPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> strWbkLast <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wbk <SPAN style="color:#00007F">As</SPAN> Workbook<br>    <br>    lngRow = 3<br>    strWbkPath = ActiveSheet.Cells(lngRow, 1) & ActiveSheet.Cells(lngRow, 2)<br>    <br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> Dir(strWbkPath) <> ""<br>        <SPAN style="color:#007F00">' Only open workbook if it is not already open</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> strWbkPath <> strWbkLast <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#007F00">' close previous workbook, if necessary</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> (wbk <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN>) <SPAN style="color:#00007F">Then</SPAN><br>                wbk.Close <SPAN style="color:#00007F">False</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> wbk = <SPAN style="color:#00007F">Nothing</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <br>            <SPAN style="color:#007F00">' I'd open the data workbook read-only so that I don't mess up the data</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> wbk = Workbooks.Open(strWbkPath, , <SPAN style="color:#00007F">True</SPAN>)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>        <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>            <SPAN style="color:#00007F">If</SPAN> .Cells(lngRow, 4) = 5 <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#007F00">' Col E first, then Col F</SPAN><br>                .Cells(lngRow, 5) = wbk.Worksheets(.Cells(lngRow, 3)).Range("D8")<br>                .Cells(lngRow, 6) = wbk.Worksheets(.Cells(lngRow, 3)).Range("D9")<br>            <SPAN style="color:#00007F">ElseIf</SPAN> .Cells(lngRow, 4) = 7 <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#007F00">' Col E first, then Col F</SPAN><br>                .Cells(lngRow, 5) = wbk.Worksheets(.Cells(lngRow, 3)).Range("E8")<br>                .Cells(lngRow, 6) = wbk.Worksheets(.Cells(lngRow, 3)).Range("E9")<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <br>            strWbkLast = strWbkPath<br>            lngRow = lngRow + 1<br>            strWbkPath = .Cells(lngRow, 1) & .Cells(lngRow, 2)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Loop</SPAN><br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[joeshu26]

Thank you Russel for taking the time and effort to put this together. You are correct my dates on my tabs are not formatted with dd/mm/yyyy but rather ex: Dec 26, 2014. I am testing your above code and am getting close! Thank you

 

[Russell Hauf]

Great! Glad to hear it.