0

I am writing a macro for Excel in Visual Basic where it can take data from a sheet and move it over to another sheet and do some calculations to it. The problem arises when I save the Excel document and exit out, the original sheet is deleted and the new sheet remains. I want to keep the original sheet, but I have no idea why it is disappearing every time I save the file.

    original = "Original"
    sheet_name = "TestSheet"

    Sheets(1).Name = original

 ' Creates a new sheet and names it based on sheet_name
    Sheets.Add After:=ActiveSheet
    Sheets(2).Name = sheet_name

    ' Copies the columns from the orginal sheet and moves it to sheet_name
    Sheets(original).Columns(1).Copy Destination:=Sheets(sheet_name).Columns(1)
    Sheets(original).Columns(3).Copy Destination:=Sheets(sheet_name).Columns(2)
    Sheets(original).Columns(6).Copy Destination:=Sheets(sheet_name).Columns(3)
    Sheets(original).Columns(10).Copy Destination:=Sheets(sheet_name).Columns(4)
    Sheets(original).Columns(12).Copy Destination:=Sheets(sheet_name).Columns(5)

    ' Copies title name to another cell
    Range("M2").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B2").Select
    Selection.Copy
    Range("M2").Select
    ActiveSheet.Paste

    ' Creates the titles of O, R, and W
    Range("M3").Select
    ActiveCell.FormulaR1C1 = "O"
    Range("M4").Select
    ActiveCell.FormulaR1C1 = "R"
    Range("M5").Select
    ActiveCell.FormulaR1C1 = "W"

    ' Calculates the data
    Range("C2").Select
    Selection.Copy
    Range("N4").Select
    ActiveSheet.Paste
    Range("O3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=R[-1]C[-11]*0.3"
    Range("O4").Select
    ActiveCell.FormulaR1C1 = "=R[-2]C[-11]*0.1"
    Range("O5").Select
    ActiveCell.FormulaR1C1 = "=R[-3]C[-11]*0.6"
    Range("O6").Select
Jason
  • 1
  • 1
    Where is the relevant code? All you included was irrelevant to your problem. – braX Jan 09 '20 at 17:55
  • Do you have any more code than this? I can't replicate the problem. – BigBen Jan 09 '20 at 17:58
  • Consider reading: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Parfait Jan 09 '20 at 18:03
  • Specifically, include the part of the code that does the actual saving and construction of the filename in your question. – braX Jan 09 '20 at 18:20
  • That is all the code that I have for the macro. Do I have to use a specific method to save the sheets – Jason Jan 09 '20 at 18:56
  • Yes. You need specific code to actually save the sheets. Check out this site, it has a rather comprehensive list of patterns. https://www.automateexcel.com/vba/vba-save-file/ – Ryan B. Jan 09 '20 at 21:43

0 Answers0