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

Reverse the steps. Copy the sheet 1st, then the cells.

Hth,
Merjet

I tried that but problem with it is the copied cells will be on a sheet that
i dont know the name of so i cant get back to that sheet and copy the cells
after i have copied the sheet. Ideally i would like to temperarily name the
active sheet, then copy the sum services sheet, go back to the original sheet
copy the cells paste them, and then rename the original sheet to its original
name.

merjet wrote:
Reverse the steps. Copy the sheet 1st, then the cells.

Hth,
Merjet

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

I tried that but problem with it is the copied cells will be on
a sheet that i dont know the name of

You can find the name of the sheet with Selection.Worksheet.Name or
Selection.Parent.Name.

Hth,
Merejt

how would i incorporate this into my code?

merjet wrote:
I tried that but problem with it is the copied cells will be on
a sheet that i dont know the name of

You can find the name of the sheet with Selection.Worksheet.Name or
Selection.Parent.Name.

Hth,
Merejt

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

how would i incorporate this into my code?

Dim aName As String
aName = Selection.Worksheet.Name
.. . . .
Sheets(aName).Range(address).Copy

On second thought, there is another way. Declare another range
variable. e.g. rngC, and set it equal to the cells you want to copy
from later.
Set rngC = . . . .
.. . . . . . .
rngC.Copy Destination:= . . . .

Hth,
Merjet