Prev: Save
Next: GetOpenFileName
From: joel on 13 Mar 2010 13:30

I left out two periods in the With Statement. I'm not sure why the
sheet name didn't get changed. I also missed the fact that the MOVE
method was creating a new workbook.





VBA Code:
--------------------



Sub mba()
Dim nme As Name

Application.ScreenUpdating = False
Application.EnableEvents = False

With Sheets("MBA")
.Range("MBAData").Copy
.Range("A80").PasteSpecial _
Paste:=xlValues
End With

Sheets("MBA").Copy After:=Sheets(Sheets.Count)
Set NewSht = Sheets(Sheets.Count)
With NewSht
.Name = "Client MBA"
.Range ("A1")
.Cells.Copy
.cellsPasteSpecial _
Paste:=xlValues
End With

Range("MBApaste").Clear
Sheets("MBA").Visible = False

Sheets("Client MBA").Move
Set NewBk = Activeworkbook

For Each nme In NewBk.Names 'delete named ranges not needed in new
'Workbook
If nme.Name Like "*_FilterDatabase" Or _
nme.Name Like "*Print_Area" Or _
nme.Name Like "*Print_Titles" Or _
nme.Name Like "*wvu.*" Or _
nme.Name Like "*wrn.*" Or _
nme.Name Like "*!Criteria" Then
Else
nme.Delete
End If
Next nme
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


--------------------


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=187201

http://www.thecodecage.com/forumz/chat.php

First  |  Prev  | 
Pages: 1 2
Prev: Save
Next: GetOpenFileName