LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 410
Default VBA to Move Sheets and save

My code is below. Here is what I ahve so far. I am trying to copy a
couple of sheets from a workbook to a new workbook to eliminate all
teh vba code, then save the file as the same name. any help woudl be
appreciated. There are a few other steps I have to go through, the
saving code is at the bottom.

Thanks,
Jay

Sub TearItDown()
Dim Nm As String
Dim FlNm As String
Dim Bk As Workbook
Set Bk = ActiveWorkbook
Nm = Bk.Name
FlNm = Bk.FullName
If ActiveWorkbook.Name = "New Item Master.xls" Then
MsgBox "You are not allowed to delete" & vbCrLf & _
" anything from this master." & vbCrLf & _
"Please save file with a new" & vbCrLf & _
"item number first"
Exit Sub
Else
Range("A5").Select
Sheet1.Range("F4") = Sheet1.Range("F4").Value * 1
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheet4.Range("A1:K82").Copy
Sheet4.Range("A1").PasteSpecial xlPasteValues
With Sheet1.Range("A1:G90").Validation
.Delete
End With
Sheet1.Range("A14:G90").Copy
Sheet1.Range("A14").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("I:V").Delete
Sheets("Cab Quantities").Delete

Dim shp As Shape
Dim myVar As Shapes

Sheet1.Activate
Count = ActiveSheet.Shapes.Count

For i = Count To 1 Step -1
ActiveSheet.Shapes(i).Delete 'myVar(i).Delete
Next i

Sheets(Array("Master", "NI Worksheet")).Select
Sheets("Master").Activate
Sheets(Array("Master", "NI Worksheet")).Copy
Set CopyBook = ActiveWorkbook
Workbooks(Nm).Close
CopyBook.SaveAs (FlNm)

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End If
End Sub
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Is there a shortcut key to move between sheets in Excel? Josh Craig Excel Discussion (Misc queries) 4 May 8th 08 04:36 PM
how do I move between sheets in a workbook using the keyboard.... hamiltoncruiser Excel Discussion (Misc queries) 2 December 12th 07 03:46 AM
Cannot Move or Copy Sheets Toys Excel Discussion (Misc queries) 1 June 20th 07 12:59 PM
Use Tab key to move between tab sheets Lisa Excel Worksheet Functions 0 January 5th 06 04:06 PM
copy/move sheets within workbook Roger B. Excel Discussion (Misc queries) 1 May 5th 05 05:43 PM


All times are GMT +1. The time now is 07:27 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"