You can use the Worksheet.SaveAs method to save an individual sheet as a
separate file. Note below that I am using a variable FileName as the name
(path) for the file to save to - you did not say what that would be but
whatever it is you can set FileName at the point indicated in the code (where
I have FileName="???"). Also, by "columns centered" I assumed you meant all
the columns (including the ones copied in) but if you only meant A1 and A2
that is easy to fix by changing the line where HorizontalAlignment is set:

Sub CopyToSheetsAndFiles()
Dim ThisCell as Range, FileName as String
For Each ThisCell in Worksheets("Sheet1").Range("A1:A300").Cells
Worksheets("report").Range("A1999:X2687").Copy
Worksheets(ThisCell.Text).Range("A1999:X2687").Pas teSpecial xlPasteValues
Application.CutCopyMode = False
With Worksheets(ThisCell.Text)
.Range("A1").Value = "Report for Section"
.Range("A2").Value = ThisCell.Text
.Range("A:X").HorizontalAlignment = xlHAlignCenter
FileName = "???"
.SaveAs FileName
End With
Next ThisCell
End Sub

--
- K Dales


"AmyTaylor" wrote:


Thanks K.Dales, that works great.

Can I ask you a further question, how could I set a macro to
automatically copy each of these sheets into individual Excel files ?
Ideally, the words "report for section" would appear on cell a1, and
I would want the sheet name to appear in cell a2 of the new file.
If possible, i would like to have the columns centered.
Is that possible ??
Thank you very much again
Amy xxx


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=535476