Don't know about 2007 but this works in 2003. I'm sure the experts can make
or more simple version of this code.
Sub CopyVisCells()
Dim ThisDate As Date, myDate As String, myfile As String
'you might like to change this to
'myfile = activesheet.name 'remove the comma at beginning of line and
' rem or delete next line
myfile = "ShortRept"
ThisDate = Int(Now)
myDate = Day(ThisDate) & "-" & Month(ThisDate) & "-" & Year(ThisDate)
myfile = myfile & " " & myDate
MsgBox myfile
Application.DisplayAlerts = False
Range("A1").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Peter\My Documents\" & myfile,
FileFormat:=xlCSV, _
CreateBackup:=False
ActiveWorkbook.Close
Selection.CurrentRegion.Select
Selection.EntireColumn.Hidden = False
Cells(1, 1).Select
Application.DisplayAlerts = True
End Sub