PDA

View Full Version : Solved: Moving Copying and Deleting A Sheet



sooty8
01-23-2009, 12:31 PM
Hi
Can you help me solve the best way to save a sheet to a new folder on the Desktop ( Folder Name - Say "Invoices 2009" ) using the code below I copy the master sheet each time I send out an Invoice the copy sheet has a different name each time based on the Invoice number. After moving the copy sheet then deleting it from the original work book.


Private Sub Add3_Click()
Application.ScreenUpdating = False
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Invoice")
Range("A1:J56").Select
Sheets("Invoice").Copy After:=Sheets(2)
ActiveSheet.Name = Range("C14").Value
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Columns("L:O").Select
Selection.EntireColumn.Hidden = True
Sheets("Invoice").Select
Range("B7").Select
Application.ScreenUpdating = True
End Sub


Many Thanks

Sooty8

Bob Phillips
01-23-2009, 12:47 PM
Private Sub Add3_Click()
Dim invnum As String
Application.ScreenUpdating = False
Dim iRow As Long
Dim ws As Worksheet
invnum = Range("C14").Value
Set ws = Worksheets("Invoice")
Sheets("Invoice").Copy
ActiveSheet.Name = invnum
Columns("L:O").Hidden = True
ActiveWorkbook.SaveAs Filename:="C:\desktop path\Invoices 2009\Invoice " & invnum
ActiveWorkbook.Close
Sheets("Invoice").Select
Range("B7").Select
Application.ScreenUpdating = True
End Sub

sooty8
01-24-2009, 03:19 AM
Hi Xld

Many thanks - Once again the Master easily solves a problem that I struggled with for over 3 Hours - I always try and solve things myself however old age and poverty is slowly but surely seeing off the memory - That is why I always visit the best Forum on the Net.
Once again many thanks your knowledge and help is much appreciated.

Sooty8

Bob Phillips
01-24-2009, 04:08 AM
Thanks Sooty8,

I admit I wasn't absolutely sure what you were trying to do, so I took a pop figuring that you would tell us more if it wasn't correct.

Have you registered for the April Excel conference, it might help you.