Dim sh as Worksheet, sh1 as Worksheet
Dim rng as Range
Dim sPath as String
sPath = Activeworkbook.Path
if right(sPath,1) < "\" then sPath = sPath & "\"
With Worksheets("Accounts")
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With
set sh = Worksheets("Summary")
for each cell in rng
sh.Range("A1").Value = cell.Value
sh.Copy
set sh1 = Activeworkbook.Worksheets(1)
sh1.Cells.copy
sh1.Cells.PasteSpecial xlValues
sh1.Parent.SaveAs sPath & _
sh1.Range("A1") & ".xls", xlworkbookNormal
sh1.close savechanges:=False
Next

--
Regards,
Tom Ogilvy

"mattri" wrote in message
...

Hi all,
I'm a newbie to VBA and need a solution to the following problem if
possible.

I have a worksheet that summarises another using sumproducts etc and
displays info for one account at a time whereas the main report displays
all accounts.

I need some vba code that will loop down the list of account numbers on
the main worksheet and each time do the following:

1) Copy account number to summary worksheet
2) Copy summary worksheet to new workbook and name workbook by account
number
3) Copy paste value for worksheet so that it has no links.
4) Repeat for each account number.

It sounds like a very complicated macro would be needed - is this
possible?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!