Trouble with copy to newly created sheet

[brownti via OfficeKB.com]

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

 

[merjet]

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

Hth,
Merjet

 

[brownti via OfficeKB.com]

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]

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

 

[brownti via OfficeKB.com]

how would i incorporate this into my code?

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

Hth,
Merejt

 

[merjet]

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