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

 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
What happens if you stop the macro after opening the workbook?
Code: 
    Dim newDoc As String
    newDoc = "NPSS work allocation sheet " & Year(Now) + 1 & ".xlsm"
    ActiveWorkbook.SaveCopyAs Filename:=newDoc
    Workbooks.Open Filename:=newDoc
 
Last edited:
Upvote 0
Yongle said:
What happens if you stop the macro after opening the workbook?
Code: 
    Dim newDoc As String
    newDoc = "NPSS work allocation sheet " & Year(Now) + 1 & ".xlsm"
    ActiveWorkbook.SaveCopyAs Filename:=newDoc
    Workbooks.Open Filename:=newDoc
Click to expand...

What do you mean, stop the macro? How do I do that as the code only runs when the button is pushed?
 
Upvote 0
As soon as you open the workbook, the layout is fine, if you are wondering about that.
 
Upvote 0
How have you set move and size for the boxes/shapes/whatever?

Also, how does the sheet look in Normal view as to Page Layout?
 
Last edited:
Upvote 0
What do you mean, stop the macro? How do I do that as the code only runs when the button is pushed?
Click to expand...

add this line
Code: 
    Dim newDoc As String
    newDoc = "NPSS work allocation sheet " & Year(Now) + 1 & ".xlsm"
    ActiveWorkbook.SaveCopyAs Filename:=newDoc
    Workbooks.Open Filename:=newDoc
    [COLOR=#ff0000]Exit Sub[/COLOR]

As soon as you open the workbook, the layout is fine, if you are wondering about that.
Click to expand...

Yes - you need to run through each stage of the macro and work out when the size changes

Humour me and do it anyway to save us chasing shadows ;)
 
Upvote 0
Now this is strange. The problem occurred when I was doing work at home on my laptop but I am now at work on a pc and it is not doing it. No idea why, but it seems to be working now as long as I do it at work.

I do have one other question, however. How do I change my current code to force the new workbook to be saved in the current file?

Code: 
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 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
 
Upvote 0
@dpaton05
When @Yongle suggested stopping the macro....you can insert a breakpoint anywhere in the code and it will stop at that point, it can then be resumed manually.
If you press ALT + F11 the code window will be opened. In the LH Bar of the RH window you can click at any line and insert said breapoint, the line will turn brown.
Otherwise you can step through the macro manually by pressing F8 to move through each line of the code...that way you can see what action has taken place !!
 
Upvote 0
I meant to say:

I do have one other question, however. How do I change my current code to force the new workbook to be saved in the current folder?

Everywhere I look tells me that this code will save to the current directory but it saves to the documents folder.
 
Upvote 0
This is with the following code

Code: 
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 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
 
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,825
Messages
6,181,189
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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