Results 1 to 2 of 2

VBA code to delete active sheet when saving with a suffix.

Threaded View

  1. #1
    Registered User
    Join Date
    07-09-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    21

    VBA code to delete active sheet when saving with a suffix.

    Hi,

    I have a worksheet for our job cards. There is a 'master job card' which saves to a folder marked 'In Progress' with a job number and customer name.

    When the all information has been entered onto the job-card-in-progress, I need to save it to another folder 'Completed' and then delete the 'in progress' worksheet.

    I have looked and tried to work in some code from other examples but I just cant seem to crack it.

    Below is my current code. Any assistance greatly appreciated.

    BTW, I am not a programmer, I learned all this from scouring the internet, so programming terminology may be lost on me.

    Sub NextInvoice()
    Range("I4").Value = Range("I4").Value + 1
    Range("C3:E10,H6:J9,G10:J11,B14:J24,B27:G31,B41:J55,I26:J31,B61:J74,C76:J76").ClearContents
    End Sub
    ==================================================================================
    Sub SaveInvWithNewNAME()
    Dim NewFilname As Variant
    'Copy Invoice To New Workbook\
    ActiveSheet.Copy
    NewFN = "S:\Job Cards\JOBS IN PROGRESS\Job" & Range("I4").Value & Range("H6") & ".xlsx"
    Range("I5").Formula = "=Today()"
    Range("I5").Value = Range("I5").Value
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextInvoice
    End Sub
    =================================================================================
    Sub Completed()
    Dim NewFilname As Variant
    'Copy Invoice To New Workbook\
    ActiveSheet.Copy
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="S:\Job Cards\Completed\Job " & Range("I4").Value & Range("H6") & "COMPLETED" & ".pdf", OpenAfterPublish:=False
    ActiveWorkbook.Close
    End Sub
    It is in this last bit of code I want to add the magic to, to delete the current sheet being used from the in progress folder.
    Last edited by Slyman; 07-11-2016 at 09:58 PM. Reason: ADDED CODE TAGS AS REQUESTED

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to copy a line to archive sheet and delete from active sheet
    By mmccra2858 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-08-2015, 09:15 PM
  2. [SOLVED] Display only active sheet when saving and sending
    By sacastiglia in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-19-2014, 05:19 PM
  3. [SOLVED] Code to close active workbook without saving
    By Nitefox in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-23-2014, 03:24 PM
  4. Replies: 2
    Last Post: 09-20-2012, 11:54 AM
  5. Delete code when saving the file
    By Bob@Sun in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-26-2010, 04:42 PM
  6. Delete Rows that contains zero and Copy active sheet to New Sheet
    By antoni in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2009, 02:02 AM
  7. [SOLVED] VBA Delete a row in active sheet Please help
    By Alexandre in forum Excel General
    Replies: 2
    Last Post: 06-08-2005, 11:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1