Macro for Creating List

[Aeonat]

Hi

I am looking for a solution which will allow me to create a long list of references to the names of documents which follow a pattern.

To clarify, I am producing a summary sheet which draws data from other documents. I am using the formula:

<code>=[SR1D.xlsx]SR!NU1 </code>

in one of the cells, and the next one down is

<code>=[SR1N.xlsx]SR!NU1 </code>

then

<code>SR2D
SR2N
SR3D
...</code>

for day and night shifts.

I would like to be able to create a macro to do it, but have very little experience in doing so, and was hoping someone could help.

Thanks in advance

-Aeonat

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this in a copy of your workbook.

It places the formulas in column A (starting at A2) of a sheet called 'Summary' (assumes 'Summary' sheet already exists).

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Make_Ref_List()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> NumDays <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 5 <SPAN style="color:#007F00">'<- Change to suit</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> Shift <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "DN"<br>    <SPAN style="color:#00007F">Const</SPAN> fBase <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "=[SR#^.xlsx]SR!NU1"<br>    <br>    Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Summary") <SPAN style="color:#007F00">'<- Change to suit</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> NumDays<br>            <SPAN style="color:#00007F">For</SPAN> j = 1 <SPAN style="color:#00007F">To</SPAN> 2<br>            .Cells(2 * i + j - 1, "A").Formula = _<br>                Replace(Replace(fBase, "#", i, 1, 1, 1), _<br>                "^", Mid(Shift, j, 1), 1, 1, 1)<br>            <SPAN style="color:#00007F">Next</SPAN> j<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[machopicho]

Something like this?

Sub test()
n = InputBox("How voices do you want to create?")
For r = 1 To n * 2 Step 2
nr = nr + 1
Cells(r, "A").Formula = "=[SR" & nr & "D.xlsx]SR!NU1"
Cells(r + 1, "A") = "=[SR" & nr & "N.xlsx]SR!NU1"
Next
nr = 0
End Sub

 

[repairman615]

Hello,

You may be able to do this with a formula:

<TABLE style="WIDTH: 402pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=536><COLGROUP><COL style="WIDTH: 402pt; mso-width-source: userset; mso-width-alt: 9801" width=536><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c8dc8c; WIDTH: 402pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=536>=INDIRECT("[SR"&IF(ROW()=1,ROW(A1),ROUND(ROW(A1)*0.5,0))&"D.xlsx]SR!"&NU1)

</TD></TR></TBODY></TABLE>

in the top cell of the summary list and then below that:

<TABLE style="WIDTH: 402pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=536><COLGROUP><COL style="WIDTH: 402pt; mso-width-source: userset; mso-width-alt: 9801" width=536><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c8dc8c; WIDTH: 402pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2853892 class=xl65 height=21 width=536>=INDIRECT("[SR"&IF(ROW()=1,ROW(A1),ROUND(ROW(A1)*0.5,0))&"N.xlsx]SR!"&NU1)</TD></TR></TBODY></TABLE>


Now select both cells and then fill down.


Could also wrap this in an IFERROR() to alert when the file doesn't exist.

-Jeff

 

[Aeonat]

Wow! When it rains it pours, but in a good way!

I took on the first solution suggested by Peter, and it works a charm (though I have no idea how it manages it).

Thank you all very much for your solutions, and saving me about a year of typing that stuff in manually!

All the best

- Aeonat