Below is my code. I have a worksheet that i want information copied to but i
want to first make a copy of the "template" worksheet then copy the
information and then rehide the "template" worksheet. So i want to copy only
visible cells in the active selection, then copy "SERVICES SUM" and name it
from the inputbox, then paste the visible cells in D22. the copied cells are
no longer copied after i copy the sheet. Any thoughts?


Sub sumsheet()
Dim Rng As Range, Rng1 As Range
Application.ScreenUpdating = False
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("SERVICES SUM").Visible = True
Sheets("services sum").Select
sheetname = InputBox("Enter desired name for copy of active sheet")
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = sheetname
Sheets(sheetname).Select
Range("D22").Select
Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False
Set Rng = Range("d22:n119")
On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("e:e").SpecialCells(xlBlanks))
On Error GoTo 0
If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete
Range("a1").Select
Sheets("services sum").Visible = False
Application.ScreenUpdating = True
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200705/1