VBA - Create a copy of current workbook in current directory

[Tiesa]

Hi everyone,

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

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"

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!

 

[Joe4]

Welcome to the Board!

Try this:

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]

Hi,​

​

see also SaveCopyAs method …​

​