Consulting

Results 1 to 3 of 3

Thread: VBA Code to replace action if MACRO is run again

  1. #1

    VBA Code to replace action if MACRO is run again

    Background: I created a MACRO to create a new spreadsheet named "DATA" which copies the information on another spreadsheet to the newly created "DATA" spreadsheet with the paste special feature of "values and number formats" (no formulas included) so that the new spreadsheet would link to a database I use.

    Here is the MACRO I wrote:

    Sub CreateDataForm()
     
    Sheets.Add.Name = "DATA"
    Sheets("DATA").Move Before:=Sheets(1)
    Sheets("ETL").Select
    Cells.Select
    Selection.Copy
    Sheets("DATA").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
    Cells.EntireColumn.AutoFit
    Application.CutCopyMode = False
     
    End Sub
    Issue: I now would like to know how to edit my MACRO so that when I rerun the button after changes are made it will delete the newly made "DATA" tab and create a new one with the new information, also named "DATA".

    I've researched looping, but I only need it to complete this action if the MACRO button is pushed again. I am new to VBA and MACROS so stating answers as simply as possible and including actual code would be very helpful.
    Last edited by SamT; 06-09-2017 at 05:15 PM.

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    Where is macro button? ETL sheet?


    Sub test()
    
        Application.DisplayAlerts = False
        On Error Resume Next
        Sheets("DATA").Delete
        On Error GoTo 0
        Application.DisplayAlerts = True
        
        Sheets("ETL").Copy Sheets(1)
        With ActiveSheet
            .Name = "DATA"
            .UsedRange.Value = .UsedRange.Value
        End With
    
    End Sub

  3. #3
    The macro button is on a third spreadsheet "Equity wire"

    Essentially the "Equity wire" sheet has several inputs in it that once filled out spits out the information (using several VLOOKUPs) to the "ETL" sheet but the information there is in a format my database can not read because it has formulas, so I created the macro to copy the information from the "ETL" to a new sheet "DATA" which is in a format that can be read by the database. But if someone made a mistake filling out the "Equity wire" inputs and wanted to rerun the macro using the button they would now have to delete the "DATA" tab otherwise it will provide an error message stating there is already a sheet named "DATA". So when the button is pushed again, I would like it to do the same process but with the new data that was entered while also replacing the original "DATA" tab. (if that makes sense?)

Posting Permissions

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