vba code to copy and paste the current workbook

[dpaton05]

I need some vba code that will make a copy of the current workbook. Can someone help me please?

 

[Michael M]

I just want to press a button and for a new document to appear and for it to be an exact copy of the already open document. The filename of the new document needs to be set to June- July of the next financial year.

Is this what you want

Sub MM1()
ActiveWorkbook.SaveAs Format(Date, "yyyy") +1 & " - " & Format(Date, "yyyy") + 2 & ".xlsm", FileFormat:=52
End Sub

 

[dpaton05]

Thanks Michael, that was almost what I wanted. I just copied a bit of code and put it infront of it to save the workbook first, before making the copy so now my code reads:

ActiveWorkbook.Save
ActiveWorkbook.SaveAs Format(Date, "yyyy") + 1 & " - " & Format(Date, "yyyy") + 2 & ".xlsm", FileFormat:=52

Why is there a fileformat:=52 on the end?

 

[Michael M]

There are a whole heap of numbers for different types of files

fileformat:=52 means save the file as a file that has macros, so that the code will be saved to the new workbook

Have a look here for all the different file enumerations


https://docs.microsoft.com/en-us/office/vba/api/Excel.XlFileFormat

 

[dpaton05]

I have code that copies the workbook but could I have a little help with code to increment the year next to the abreviated month name please Michael? They are for instance, Dec 16, Jan 17, Feb 17 etc.

 

[Michael M]

Sorry, you'll have to be a bit clearer with the request ??

 

[dpaton05]

Sorry to be a bit confusing Michael. Here is a picture of my spreadsheet https://www.screencast.com/t/fbaaeCHqkalQ. It appears all bunched together as I squeezed it together to fit it all in the screen shot. When a costing is entered and copied to the relevant sheet, it is put in one of the sheets along the bottom, according to the date of the costing.

For the entry dated 5.11.18, it is placed in the november sheet of 2018. When the end of the financial year is reached, ie, June, I want to press the new financial year document button in the middle of the page and I have code that will save it in it's current state, then save it again for the file of the new financial year.

The problem is where the sheet names are July 2018 to June 2019, I want them to be for the following financial year document so if it is clicked when this document is open, the new document will have the sheet names of July 2019 to June 2020. If the button is clicked when the document that has the sheet names July 2019 to June 2020, the new document will be July 2020 to June 2021 and so forth. Therefore, it will just be incrementing by one year each time a new document is made.

I want the new document to be an exact copy of the previous one, except having the year numbers increamented by 1.

I don't know what the code would be to do that, could you help me please?

 

[Michael M]

you could incorporate this into your workbook save code

sub MM1
ActiveWorkbook.SaveAs Format(Date, "yyyy") + 1 & " - " & Format(Date, "yyyy") + 2 & ".xlsm", FileFormat:=52
Dim ws As Worksheet, yr As Integer, ans As Date
yr = InputBox("Please enter year:")
  ans = DateSerial(yr, 7, 1) 'y
  For Each ws In Worksheets
    If ws.Name <> "Home" And ws.Name <> "Analysis" Then
        ws.Name = Format(ans, "MMMM YYYY")
        ans = DateAdd("m", 1, ans)
    End If
    Next ws
end sub

 

[dpaton05]

This is the code I have worked out for the new tool button and this does what I wanted, opens a new workbook and renames everything in it.

Private Sub cmdNewTool_Click()
Dim newDoc As String
    newDoc = "NPSS work allocation sheet " & Year(Now) + 1 & ".xlsm"

    
  
    ActiveWorkbook.SaveCopyAs Filename:=newDoc
    
    Workbooks.Open Filename:=newDoc
    
    With Workbooks(newDoc)
        .Sheets("July " & Range("E18")).Name = "July " & Year(Now) + 1
            With Sheets("July " & Year(Now) + 1)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "July " & Year(Now) + 1
            End With
            
        .Sheets("August " & Range("E18")).Name = "August " & Year(Now) + 1
            With Sheets("August " & Year(Now) + 1)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "August " & Year(Now) + 1
            End With
            
        .Sheets("September " & Range("E18")).Name = "September " & Year(Now) + 1
            With Sheets("September " & Year(Now) + 1)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "September " & Year(Now) + 1
            End With
            
        .Sheets("October " & Range("E18")).Name = "October " & Year(Now) + 1
            With Sheets("October " & Year(Now) + 1)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "October " & Year(Now) + 1
            End With
            
        .Sheets("November " & Range("E18")).Name = "November " & Year(Now) + 1
            With Sheets("November " & Year(Now) + 1)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "November " & Year(Now) + 1
            End With
            
        .Sheets("December " & Range("E18")).Name = "December " & Year(Now) + 1
            With Sheets("December " & Year(Now) + 1)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "December " & Year(Now) + 1
            End With
             
        .Sheets("January " & Range("E18") + 1).Name = "January " & Year(Now) + 2
            With Sheets("January " & Year(Now) + 2)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "January " & Year(Now) + 2
            End With
               
        .Sheets("February " & Range("E18") + 1).Name = "February " & Year(Now) + 2
            With Sheets("February " & Year(Now) + 2)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "February " & Year(Now) + 2
            End With
            
        .Sheets("March " & Range("E18") + 1).Name = "March " & Year(Now) + 2
            With Sheets("March " & Year(Now) + 2)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "March " & Year(Now) + 2
            End With
                    
        .Sheets("April " & Range("E18") + 1).Name = "April " & Year(Now) + 2
            With Sheets("April " & Year(Now) + 2)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "April " & Year(Now) + 2
            End With
     
        .Sheets("May " & Range("E18") + 1).Name = "May " & Year(Now) + 2
            With Sheets("May " & Year(Now) + 2)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "May " & Year(Now) + 2
            End With
            
        .Sheets("June " & Range("E18") + 1).Name = "June " & Year(Now) + 2
            With Sheets("June " & Year(Now) + 2)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "June " & Year(Now) + 2
            End With
            
        .Sheets("All Costings").Range("A4:E2000").Clear
                    
    End With
    
       
    
End Sub

I do however, like the message box to allow you to put in the required year and your code looks a lot less complex. I knew there would be a shorter way to do it but I didn't know how. I am trying to learn as much as I can about excel and have several questions about the code.

1. Why do you need FileFormat:=52 if you have already specified the file extension to be a .xlsm?
2. If you have already specified that the filename will be next year - following year in the seond line, why then in the 4th line of code does it allow for input of the year through a message box? Won't this mean that the year of the file will be the following 2 years, but the sheet names could be different years?
3. What do these 2 lines of code do?


ans = DateSerial(yr, 7, 1) 'y
ans = DateAdd("m", 1, ans)

Thanks Michael.

 

[Michael M]

Indon't see any message / input box lines in the posted code ???
If you use F8 to step through the code, then hover the mouse ove the ans variable you will see what they mean

 

[dpaton05]

Sorry Michael, I should have been more clear.

That is the code I came up with and I wanted to use your code with the message box as it looked more tidy and it has the extra feature of being able to enter the year. Is your code effectively the same as my code without the message box?