+ Reply to Thread
Results 1 to 2 of 2

Macro to Extract Information

Hybrid View

  1. #1
    Registered User
    Join Date
    03-31-2014
    Location
    Waco, Tx
    MS-Off Ver
    Excel 2013
    Posts
    12

    Macro to Extract Information

    Hi guys, I'm trying to write a macro to extract the information I have input into another workbook as I press save. Below is the code currently written and it works great, now I'm trying to finish with this last part. Thanks for any help.

    Sub SaveInvWithNewName()
        Dim NewFN As String
        Dim wb As Workbook
        Dim ws As Worksheet
         'Copy Invoice to a new workbook
        NewFN = "C:\Users\Job\Documents\GrubGut\GrubGut Financials\Invoices\" & Range("F6").Value & ".xlsx"
        
        Set ws = ActiveSheet
        Set wb = Workbooks.Add
        ws.Copy before:=wb.Sheets(1)
        wb.SaveAs NewFN, FileFormat:=51
        wb.Close
    End Sub
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        Cancel = True
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
        End With
        Call SaveInvWithNewName
        With Sheets("Invoice")
            .Range("F6").Value = Left(.Range("F6").Value, 2) & CLng(Right(.Range("F6").Value, 5)) + 1
            .Range("B9:E9").ClearContents
            .Range("F5").ClearContents
        End With
        With Application
            .ScreenUpdating = True
            .DisplayAlerts = True
        End With
    End Sub
    
    Private Sub Workbook_Open()
    With Sheets("Invoice")
        .Range("F6").Value = Left(.Range("F6").Value, 2) & CLng(Right(.Range("F6").Value, 5)) + 1
    End With
    End Sub
    Sub SaveForDeveloper()
    Dim str As String
    
    str = Application.GetSaveAsFilename
    Application.EnableEvents = False
    ThisWorkbook.SaveAs Filename:=str, FileFormat:=52
    Application.EnableEvents = True
    
    End Sub

  2. #2
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Macro to Extract Information

    Quote Originally Posted by brazilianch View Post
    I'm trying to finish with this last part. Thanks for any help.
    Could you provide more details? What is wrong with the last part? Also would you be able to upload a workbook for forum users to test (remove any personal information first)?
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Information Extract
    By mfjanoo in forum Excel General
    Replies: 5
    Last Post: 04-10-2013, 04:47 AM
  2. Extract information from a website
    By wieslaw in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-03-2013, 05:46 AM
  3. Can I create an excel macro to search & extract certain information from a pdf file?
    By razabear in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-11-2011, 02:37 AM
  4. Replies: 2
    Last Post: 10-23-2008, 11:19 AM
  5. Extract information from the list
    By 0-0 Wai Wai ^-^ in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-12-2006, 07:10 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