Using a Loop Statement to Pass Data between WKS and Output File
 

I have a spreadsheet "Customers" with Customer account numbers in Rang
(A1:A90).
On Sheet "Template" I have some sales data which uses the accoun
number occupying Wks."Template".Range("A1") to perform some looku
calculations.
I want to use a Loop statement to Pass the Customer code in the
"Customers" spreadsheet to the Template sheet and then output the fil
toa specified path such as : "C:\MyReports\".


Currently I use 1 sheet per customer and a lot of code to output thes
files in a macro. But it strikes me this could be done with a loo
statement pretty easily.

Can anyone put me out of this misery? would really aappreciate it.
Cheer

--
Message posted from http://www.ExcelForum.com

Using a Loop Statement to Pass Data between WKS and Output File
 

Belzedar

This is pretty basic and assumes that the macro is in the workbook customer.xls. It also assumes that template.xls is in the same directory as the output file (myreports). For want of any other knowledge, the output files are named as per the customer id.

Hopefully it will give you enough to get started.

Tony

Sub aaa()
ChDir "c:\myreports"

Range("a1").Select
While Not IsEmpty(ActiveCell)
aa = ActiveCell.Value
Workbooks.Open "template.xls"
Range("a1").Value = aa
ActiveWorkbook.SaveAs FileName:=aa, FileFormat:=xlNormal
ActiveWorkbook.Close
ActiveCell.Offset(1, 0).Select
Wend



End Sub

Using a Loop Statement to Pass Data between WKS and Output File
 

Tony,
Am trying out the code as we speak. Many thanks for the this it could
well save me hours of laborious macr building.

I'm really just starting out on this road so many thanks.
Damien


---
Message posted from http://www.ExcelForum.com/