PDA

View Full Version : [SOLVED] VBA Code to replace action if MACRO is run again



foxyginger
06-09-2017, 03:19 PM
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.

mana
06-09-2017, 03:53 PM
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

foxyginger
06-09-2017, 04:21 PM
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?)