VBA To copy active workbook to a new.

[Szvoboda]

Hello everyone,

On my work I use a workbook to logg specific data and information during the day. At midnight I send this report and start a new one. I have made a simple macro and locked it to a button. This copy the active workbook, moves some specific data, change date, clear the fields etc.

However.... After I used it ones in the original workbook, it will always open the original file at the same time as it makes a new copy.

When I use the button I create Book1, all works fine.
Next day, I use the button to create Book2, and then in opens the original file as well.
Book 3 same thing as I said about Book2, and on and on.

Also when I move the original file, the macro stops working, as it allways seems to refer to the original file. All the copying of numbers etc. works fine.


This is the code I use:

Sub Create_New_DVR()


ThisWorkbook.Sheets.Copy


Range("C2").Value = DateAdd("d", 1, CDate(Range("C2")))

Range("S18").Copy Range("R18")
Range("R20").Copy Range("S27")
Range("R24:S24").Copy Range("R26:S26")

Range("S18").ClearContents
Range("R20").ClearContents
Range("R23:S24").ClearContents


Range("Q13:Q13").ClearContents
Range("B11:M100").ClearContents

Range("R19").Value = 0



End Sub

It contains 2 sheets, "DVR" and "INFO"

 

[Szvoboda]

I like the macro to copy the code as well to the new workbook, so I can send that file to any computer and use the file on that computer.