VBA - Create a copy of current workbook in current directory

Tiesa

New Member
Joined
May 5, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

Below is a screengrab of a very basic version of the excel workbook I'm trying to create

Capture.PNG



The goal is to have the a Button that does the following things:
  1. Clicking on the Button creates a copy of this workbook, including VBA
  2. The workbook needs to be saved in the same directory, with the directory not being specific
  3. The name of the new copied workbook needs to be taken from cells B6, then B7, then file extention . Ie. in this example it would be "VW Golf 3RTY568.xlsm"
  4. In this newly created "VW Golf 3RTY568.xlsm" file, I want to clear cell B2, B3 and B4 and cut&paste cell B6 and B7 to cell B3 and B4. This part I have already got working through recording a macro
  5. The final requirement is that the new "VW Golf 3RTY568.xlsm" workbook also has the same button element that performs step 1 to 4
So in short: clicking on the button needs to create a copy of the workbook (named after the Trade-in Car & License plate), and formats the cells so that the user can easily enter a new trade-in car and repeat the process by clicking the button.


The issue I am running into is in creating a copy of the workbook in the same directory, and ensuring that the copy (and all subsequent copies) have a functioning Button

This is my current code for creating a copy of the file, which already does not work correctly - it insteads create a copy in "/My Documents"

VBA Code:
Sub createnewWB()
    Dim thisWb As Workbook
    Dim newWBname As String
    
    newWBname = Range("B7")
    
    Set thisWb = ActiveWorkbook
    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=thisWb.Path & newWBname
    
End Sub


Sub buttoncalls()

Call createnewWB

End Sub



Thank you in advance for any assistance!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,508
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Try this:
VBA Code:
Sub createnewWB()
    Dim wbPath As String
    Dim newWBname As String
   
    newWBname = Range("B7")
   
    wbPath = ActiveWorkbook.Path & "\"
    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=wbPath & newWBname
   
End Sub
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
902
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
see also SaveCopyAs method …​
 

Watch MrExcel Video

Forum statistics

Threads
1,132,640
Messages
5,654,537
Members
418,138
Latest member
agnesegras

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
Top