Format changing when file is copied

D

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,392
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a button on my spreadsheet that makes a copy of the spreadsheet, here is a screenshot https://www.screencast.com/t/kzrb83Vrar. When I try and make a new document it copies the spreadsheet but the 6 boxes from the Add 10% box get squashed up and I have no idea why. Here is a screenshot of what the file looks like when the new document is made https://www.screencast.com/t/IdCYyLBlVVAT.

The code behind the button that makes the new document is:

Code: 
Dim newDoc As String
    newDoc = "NPSS work allocation sheet " & Year(Now) + 1 & ".xlsm"

    
  
    ActiveWorkbook.SaveCopyAs Filename:=newDoc
    
    Workbooks.Open Filename:=newDoc
    
    With Sheets("home")
        .Range("B20") = "July " & Year(Now)
        .Range("B21") = "August " & Year(Now)
        .Range("B22") = "September " & Year(Now)
        .Range("B23") = "October " & Year(Now)
        .Range("B24") = "November " & Year(Now)
        .Range("B25") = "December " & Year(Now)
        .Range("E20") = "January " & Year(Now) + 1
        .Range("E21") = "February " & Year(Now) + 1
        .Range("E22") = "March " & Year(Now) + 1
        .Range("E23") = "April " & Year(Now) + 1
        .Range("E24") = "May " & Year(Now) + 1
        .Range("E25") = "June " & Year(Now) + 1
    End With
        
        
   
    
    With Workbooks(newDoc)
        .Sheets("July " & Range("E18")).Name = "July " & Year(Now)
            With Sheets("July " & Year(Now))
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "July " & Year(Now) + 1
            End With
            
        .Sheets("August " & Range("E18")).Name = "August " & Year(Now)
            With Sheets("August " & Year(Now))
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "August " & Year(Now)
            End With
            
        .Sheets("September " & Range("E18")).Name = "September " & Year(Now)
            With Sheets("September " & Year(Now))
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "September " & Year(Now) + 1
            End With
            
        .Sheets("October " & Range("E18")).Name = "October " & Year(Now)
            With Sheets("October " & Year(Now))
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "October " & Year(Now) + 1
            End With
            
        .Sheets("November " & Range("E18")).Name = "November " & Year(Now)
            With Sheets("November " & Year(Now))
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "November " & Year(Now) + 1
            End With
            
        .Sheets("December " & Range("E18")).Name = "December " & Year(Now)
            With Sheets("December " & Year(Now))
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "December " & Year(Now) + 1
            End With
             
        .Sheets("January " & Range("E18") + 1).Name = "January " & Year(Now) + 1
            With Sheets("January " & Year(Now) + 1)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "January " & Year(Now) + 2
            End With
               
        .Sheets("February " & Range("E18") + 1).Name = "February " & Year(Now) + 1
            With Sheets("February " & Year(Now) + 1)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "February " & Year(Now) + 2
            End With
            
        .Sheets("March " & Range("E18") + 1).Name = "March " & Year(Now) + 1
            With Sheets("March " & Year(Now) + 1)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "March " & Year(Now) + 2
            End With
                    
        .Sheets("April " & Range("E18") + 1).Name = "April " & Year(Now) + 1
            With Sheets("April " & Year(Now) + 1)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "April " & Year(Now) + 2
            End With
     
        .Sheets("May " & Range("E18") + 1).Name = "May " & Year(Now) + 1
            With Sheets("May " & Year(Now) + 1)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "May " & Year(Now) + 2
            End With
            
        .Sheets("June " & Range("E18") + 1).Name = "June " & Year(Now) + 1
            With Sheets("June " & Year(Now) + 1)
                .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

 
Sort by date Sort by votes
Change this line
Code: 
ActiveWorkbook.SaveCopyAs Filename:=newDoc

to
Code: 
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & newDoc
 
Last edited:
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
That works thanks in that it saves the file to the correct directory but I also get an error message when I press the button: "Run time error 1004: Sorry, we couldn't find NPSS work allocation sheet 2020.xlsm. Is it possible it was moved, renamed or deleted?"
 
Upvote 0
If I step through the code, when it gets to the line workbooks.open filename:=Newdoc, I get the error message of Application defined or object defined error.
 
Upvote 0
So I'm guessing this line at the start...

"NPSS work allocation sheet " & Year(Now) + 1 & ".xlsm"
Click to expand...
is incorrect...that's where you nnned to check first
 
Upvote 0
What do you mean? It used to work.
 
Last edited:
Upvote 0
What do you mean by "that's where you nnned to check first"
 
Upvote 0
Michael M said:
Change this line
Code: 
ActiveWorkbook.SaveCopyAs Filename:=newDoc

to
Code: 
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & newDoc
Click to expand...


This doesn't do anything. It still saves to the documents folder.
 
Upvote 0
Please help Michael, I don't know what to do.

This is the code I have but it still won't work

Code: 
Private Sub cmdNewTool_Click()
ActiveSheet.Unprotect Password:="costings"
Dim W As Workbook
Dim newDoc As String
    Set W = ActiveWorkbook
'Dim wb As Workbook
    'Set wb = ActiveWorkbook
    newDoc = "NPSS work allocation sheet " & Year(Now) + 1 & ".xlsm"
    'ActiveWorkbook.SaveCopyAs Filename:=newDoc
    ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & newDoc
      
    Workbooks.Open Filename:=newDoc
    
    With Sheets("home")
        .Range("B20") = "July " & Year(Now)
        .Range("B21") = "August " & Year(Now)
        .Range("B22") = "September " & Year(Now)
        .Range("B23") = "October " & Year(Now)
        .Range("B24") = "November " & Year(Now)
        .Range("B25") = "December " & Year(Now)
        .Range("E20") = "January " & Year(Now) + 1
        .Range("E21") = "February " & Year(Now) + 1
        .Range("E22") = "March " & Year(Now) + 1
        .Range("E23") = "April " & Year(Now) + 1
        .Range("E24") = "May " & Year(Now) + 1
        .Range("E25") = "June " & Year(Now) + 1
    End With
    
    With Workbooks(newDoc)
        .Sheets("July " & Range("E18")).Name = "July " & Year(Now)
            With Sheets("July " & Year(Now))
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "July " & Year(Now) + 1
            End With
            
        .Sheets("August " & Range("E18")).Name = "August " & Year(Now)
            With Sheets("August " & Year(Now))
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "August " & Year(Now)
            End With
            
        .Sheets("September " & Range("E18")).Name = "September " & Year(Now)
            With Sheets("September " & Year(Now))
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "September " & Year(Now) + 1
            End With
            
        .Sheets("October " & Range("E18")).Name = "October " & Year(Now)
            With Sheets("October " & Year(Now))
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "October " & Year(Now) + 1
            End With
            
        .Sheets("November " & Range("E18")).Name = "November " & Year(Now)
            With Sheets("November " & Year(Now))
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "November " & Year(Now) + 1
            End With
            
        .Sheets("December " & Range("E18")).Name = "December " & Year(Now)
            With Sheets("December " & Year(Now))
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "December " & Year(Now) + 1
            End With
             
        .Sheets("January " & Range("E18") + 1).Name = "January " & Year(Now) + 1
            With Sheets("January " & Year(Now) + 1)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "January " & Year(Now) + 2
            End With
               
        .Sheets("February " & Range("E18") + 1).Name = "February " & Year(Now) + 1
            With Sheets("February " & Year(Now) + 1)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "February " & Year(Now) + 2
            End With
            
        .Sheets("March " & Range("E18") + 1).Name = "March " & Year(Now) + 1
            With Sheets("March " & Year(Now) + 1)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "March " & Year(Now) + 2
            End With
                    
        .Sheets("April " & Range("E18") + 1).Name = "April " & Year(Now) + 1
            With Sheets("April " & Year(Now) + 1)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "April " & Year(Now) + 2
            End With
     
        .Sheets("May " & Range("E18") + 1).Name = "May " & Year(Now) + 1
            With Sheets("May " & Year(Now) + 1)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "May " & Year(Now) + 2
            End With
            
        .Sheets("June " & Range("E18") + 1).Name = "June " & Year(Now) + 1
            With Sheets("June " & Year(Now) + 1)
                .Range("A4:E2000").Clear
                .Range("A1").Value = "501 NPSS " & "June " & Year(Now) + 2
            End With
            
        .Sheets("All Costings").Range("A4:E2000").Clear
                    
    End With
    
W.Activate
ActiveSheet.Protect Password:="costings"

End Sub

It says that it can't find the file called NPSS work allocation sheet 2020.xlsm, despite it already being copied to the correct directory.
 
Last edited:
Upvote 0
CAn you upload to Dropbox ??
I'll take a look over the weekend
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Using index and match from another source excel file
2
Replies
10
Views
708
Peter_SSs
Peter_SSs
D
  • Question Question
conditional formatting of holidays
Replies
2
Views
132
daveyc18
D
M
  • Question Question
Calc cumulative by month
Replies
6
Views
205
mylamly26
M
M
  • Solved
Nested Xlookup not returning all data
Replies
2
Views
469
Mat1968
M
D
  • Solved
Clear contents rows macro
Replies
7
Views
85
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,828
Messages
6,181,209
Members
453,022
Latest member
RobertV1609

Share this page

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