VBA To copy active workbook to a new.

Szvoboda

New Member
Joined
Mar 10, 2019
Messages
2
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:

PHP:
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"
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top