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

  • Thread starter Thread starter Belzeddar
  • Start date Start date
B

Belzeddar

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
 
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
 
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
 
Back
Top