Creating a Loop for multiple rows

[Loftus74]

Morning all,

Forgive me if this is incredibly obvious, but I am a VBA novice!

I am trying to create multiple invoices from my core data using an invoice template. I can get my macro to create one invoice but am unsure how to get it to loop through the remaining data.

The code I originally used for the first invoice is below and any help would be much appreciated!

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 9"><meta name="Originator" content="Microsoft Word 9"><link rel="File-List" href="file:///C:/DOCUME%7E1/PAUL%7E1.GLY/LOCALS%7E1/Temp/msoclip1/01/clip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin-top:0cm; margin-right:0cm; margin-bottom:10.0pt; margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:Calibri; mso-fareast-font-family:"Times New Roman"; mso-bidi-font-family:"Times New Roman";} @page Section1 {size:595.3pt 841.9pt; margin:72.0pt 72.0pt 72.0pt 72.0pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> Sub Prepare_Invoice()
'' Prepare_Invoice Macro
<!--[if !supportEmptyParas]-->'<o></o>
' Sheets("Invoice").Select
Range("A10").Select
ActiveCell.FormulaR1C1 = "=+Data!R[-7]C[25]"
etc


invoicenumber = Range("c28").Value
invoicename = Range("a10").Value


ActiveWorkbook.saveas Filename:= _
"C:\Users\Bob Bell\Documents\" & invoicenumber & " " & invoicename & ".xls", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End Sub[FONT=&quot]<o></o>[/FONT]

 

[shyrath]

A very simple loop would be somthing like this:

Sub loopinv()

For Each c In Worksheets("Sheet2").Range("A4:A6")

invoicename = c.Value

'Do this
MsgBox invoicename

Next c



End Sub

Excel Workbook
	A	B
1
2
3	InvoiceName	Invoicenumber
4	A1	123
5	A2	124
6	A3	125
Sheet2

 

[ravishankar]

Hi
Assuming your list of invoice names are in col Y row 7 onwards and invoice numbers in col Z (of sheet data)
try the following codes

Sub Prepare_Data()
Dim x As Long, a As Long
x = Sheets("Data").Cells(Rows.Count, 25).End(xlUp).Row
For a = 7 To x
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Bob Bell\Documents\" & Sheets("Data").Cells(a, 26) & " " & Sheets("Data").Cells(a, 25) & ".xls", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Next a
End Sub

Ravi