Save workbook but as values only

DipDip

Board Regular
Joined
Jan 23, 2015
Messages
71
Office Version
  1. 365
Platform
  1. Windows
Hiya,
So I've found the following code from another thread, and adapted it:

VBA Code:
Sub SaveAsValues()
    Dim NewBook As Workbook
    Dim CurrentFile As String
    Dim NewFile As String
            
For i = 1 To ActiveWorkbook.Sheets.Count

ActiveWorkbook.Sheets(i).Activate

    Range("A1:AL198").Select
    
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("A1").Select
    
Next i

    ActiveWorkbook.Worksheets(1).Activate
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    CurrentFile = ActiveWorkbook.FullName
 
    NewFile = "TNew Worksheet"
    ActiveWorkbook.SaveAs Filename:="C:\Test\" & NewFile & ".xlsx", FileFormat:=xlOpenXMLWorkbook
    
    Application.DisplayAlerts = True
    Set NewBook = ActiveWorkbook
    Workbooks.Open CurrentFile
    NewBook.Close
   
    Application.ScreenUpdating = True
End Sub

However, I want it to just copy cells A1:AL198 as below line 198, is a load of calculations to get the top bit working. Can anyone help me with this?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,829
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Change Selection.PasteSpecial to Range("A199:AL396") or what you want.
I think you can remove
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

And change two next line to
VBA Code:
Range("A199:AL396").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 

DipDip

Board Regular
Joined
Jan 23, 2015
Messages
71
Office Version
  1. 365
Platform
  1. Windows
Change Selection.PasteSpecial to Range("A199:AL396") or what you want.
I think you can remove
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

And change two next line to
VBA Code:
Range("A199:AL396").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Unfortunately, that didn't work :(
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
As you are asking a question I suspect that your code does not work, but you have not told us anything. ;)
Care to explain what is not working?
 

DipDip

Board Regular
Joined
Jan 23, 2015
Messages
71
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

As you are asking a question I suspect that your code does not work, but you have not told us anything. ;)
Care to explain what is not working?
It still copies over the information from below line 198.

I replaced the above quoted code with the following (unless I've not followed instructions correctly):

VBA Code:
Sub SaveAsValues()
    Dim NewBook As Workbook
    Dim CurrentFile As String
    Dim NewFile As String
            
For i = 1 To ActiveWorkbook.Sheets.Count

ActiveWorkbook.Sheets(i).Activate

    Range("A1:AL198").Select
    
    Selection.Copy
    Range("A1:AL198").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("A1").Select
    
Next i

    ActiveWorkbook.Worksheets(1).Activate
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    CurrentFile = ActiveWorkbook.FullName
 
    NewFile = "TNew Worksheet"
    ActiveWorkbook.SaveAs Filename:="C:\Test\" & NewFile & ".xlsx", FileFormat:=xlOpenXMLWorkbook
    
    Application.DisplayAlerts = True
    Set NewBook = ActiveWorkbook
    Workbooks.Open CurrentFile
    NewBook.Close
   
    Application.ScreenUpdating = True
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
All this section of code is doing, is converting A1:AL198 to values on every sheet in the workbook, nothing more.
VBA Code:
For i = 1 To ActiveWorkbook.Sheets.Count

ActiveWorkbook.Sheets(i).Activate

    Range("A1:AL198").Select
    
    Selection.Copy
    Range("A1:AL198").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("A1").Select
    
Next i
Having done that it saves the workbook with a new name.
 

DipDip

Board Regular
Joined
Jan 23, 2015
Messages
71
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

All this section of code is doing, is converting A1:AL198 to values on every sheet in the workbook, nothing more.
VBA Code:
For i = 1 To ActiveWorkbook.Sheets.Count

ActiveWorkbook.Sheets(i).Activate

    Range("A1:AL198").Select
   
    Selection.Copy
    Range("A1:AL198").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
   
    Range("A1").Select
   
Next i
Having done that it saves the workbook with a new name.
For some reason when I am doing it, it's also copying data from A199:AL300, which I don't want it to do. Nothing else in the worksheet, beyond this, so I'm thinking it's copying the whole sheet and not just the selection. Not sure what is going wrong then.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
Are you saying that the formulae in A199:AL300 are getting converted into values?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,829
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
IF you want only copy A1:AL198 and Clearing others. You Should add
Range("A199:AL" & Cells(Rows.count, 1).end(xlup).row ).Clearcontents
After
PasteSpecial Lines
VBA Code:
Sub SaveAsValues()
    Dim NewBook As Workbook
    Dim CurrentFile As String
    Dim NewFile As String
            
For i = 1 To ActiveWorkbook.Sheets.Count

ActiveWorkbook.Sheets(i).Activate

    Range("A1:AL198").Select
    
    Selection.Copy
    Range("A1:AL198").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A199:AL" & Cells(Rows.count, 1).end(xlup).row ).Clearcontents
    Range("A1").Select
    
Next i

    ActiveWorkbook.Worksheets(1).Activate
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    CurrentFile = ActiveWorkbook.FullName
 
    NewFile = "TNew Worksheet"
    ActiveWorkbook.SaveAs Filename:="C:\Test\" & NewFile & ".xlsx", FileFormat:=xlOpenXMLWorkbook
    
    Application.DisplayAlerts = True
    Set NewBook = ActiveWorkbook
    Workbooks.Open CurrentFile
    NewBook.Close
   
    Application.ScreenUpdating = True
End Sub
 
Solution

DipDip

Board Regular
Joined
Jan 23, 2015
Messages
71
Office Version
  1. 365
Platform
  1. Windows
IF you want only copy A1:AL198 and Clearing others. You Should add
Range("A199:AL" & Cells(Rows.count, 1).end(xlup).row ).Clearcontents
After
PasteSpecial Lines
VBA Code:
Sub SaveAsValues()
    Dim NewBook As Workbook
    Dim CurrentFile As String
    Dim NewFile As String
         
For i = 1 To ActiveWorkbook.Sheets.Count

ActiveWorkbook.Sheets(i).Activate

    Range("A1:AL198").Select
 
    Selection.Copy
    Range("A1:AL198").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A199:AL" & Cells(Rows.count, 1).end(xlup).row ).Clearcontents
    Range("A1").Select
 
Next i

    ActiveWorkbook.Worksheets(1).Activate
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    CurrentFile = ActiveWorkbook.FullName

    NewFile = "TNew Worksheet"
    ActiveWorkbook.SaveAs Filename:="C:\Test\" & NewFile & ".xlsx", FileFormat:=xlOpenXMLWorkbook
 
    Application.DisplayAlerts = True
    Set NewBook = ActiveWorkbook
    Workbooks.Open CurrentFile
    NewBook.Close

    Application.ScreenUpdating = True
End Sub
Maabadi, you are a genius! Thank you to everyone who contributed to this :).

Although just testing this now with the other excel macro-enabled file that I wanted to do this with, it won't work as it comes up with Run-time error 1004', We can't do that to a merged cell. Then when I click debug. Selection.Copy is highlighed in yellow.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,731
Members
417,108
Latest member
Thein Than

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