Save as Excel with same format, but no formula
up vote
0
down vote
favorite
I have a code that extracts a tab from a workbook and saves the tab as a separate sheet. Everything is working fine for me, except for the fact that the formulas are also extracted to the new sheet. How can I change the code mentioned below to save the sheet in the same format, but without any formulas?
Sub PrintFile2()
'check if folder exists
If Dir("C:Excel Workpaper", vbDirectory) = "" Then
MkDir "C:Excel Workpaper"
End If
'print to defined folder
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim wb As Workbook
Sheets("Calculations").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "C:Excel Workpaper " & Range("B7").Text & " - Excel Workpaper",
FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = False
Application.ScreenUpdating = True
.Close False
End With
End Sub
excel excel-vba excel-formula
asked yesterday
Santhosh
12
add a comment |
up vote
0
down vote
favorite
I have a code that extracts a tab from a workbook and saves the tab as a separate sheet. Everything is working fine for me, except for the fact that the formulas are also extracted to the new sheet. How can I change the code mentioned below to save the sheet in the same format, but without any formulas?
Sub PrintFile2()
'check if folder exists
If Dir("C:Excel Workpaper", vbDirectory) = "" Then
MkDir "C:Excel Workpaper"
End If
'print to defined folder
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim wb As Workbook
Sheets("Calculations").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "C:Excel Workpaper " & Range("B7").Text & " - Excel Workpaper",
FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = False
Application.ScreenUpdating = True
.Close False
End With
End Sub
excel excel-vba excel-formula
asked yesterday
Santhosh
12
If i understood your question you can read this post... stackoverflow.com/questions/14981582/… hope this helps
– Ferdinando
yesterday
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a code that extracts a tab from a workbook and saves the tab as a separate sheet. Everything is working fine for me, except for the fact that the formulas are also extracted to the new sheet. How can I change the code mentioned below to save the sheet in the same format, but without any formulas?
Sub PrintFile2()
'check if folder exists
If Dir("C:Excel Workpaper", vbDirectory) = "" Then
MkDir "C:Excel Workpaper"
End If
'print to defined folder
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim wb As Workbook
Sheets("Calculations").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "C:Excel Workpaper " & Range("B7").Text & " - Excel Workpaper",
FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = False
Application.ScreenUpdating = True
.Close False
End With
End Sub
excel excel-vba excel-formula
asked yesterday
Santhosh
12
I have a code that extracts a tab from a workbook and saves the tab as a separate sheet. Everything is working fine for me, except for the fact that the formulas are also extracted to the new sheet. How can I change the code mentioned below to save the sheet in the same format, but without any formulas?
Sub PrintFile2()
'check if folder exists
If Dir("C:Excel Workpaper", vbDirectory) = "" Then
MkDir "C:Excel Workpaper"
End If
'print to defined folder
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim wb As Workbook
Sheets("Calculations").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "C:Excel Workpaper " & Range("B7").Text & " - Excel Workpaper",
FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = False
Application.ScreenUpdating = True
.Close False
End With
End Sub
excel excel-vba excel-formula
excel excel-vba excel-formula
asked yesterday
Santhosh
12
asked yesterday
Santhosh
12
asked yesterday
Santhosh
12
asked yesterday
Santhosh
12
asked yesterday
Santhosh
12
12
If i understood your question you can read this post... stackoverflow.com/questions/14981582/… hope this helps
– Ferdinando
yesterday
add a comment |
If i understood your question you can read this post... stackoverflow.com/questions/14981582/… hope this helps
– Ferdinando
yesterday
If i understood your question you can read this post... stackoverflow.com/questions/14981582/… hope this helps
– Ferdinando
yesterday
If i understood your question you can read this post... stackoverflow.com/questions/14981582/… hope this helps
– Ferdinando
yesterday
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
Check this snippet, this code will change formula to values.
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Select
With sh.UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
Next sh
answered yesterday
Arun Banakar
412
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
Check this snippet, this code will change formula to values.
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Select
With sh.UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
Next sh
answered yesterday
Arun Banakar
412
add a comment |
up vote
1
down vote
Check this snippet, this code will change formula to values.
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Select
With sh.UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
Next sh
answered yesterday
Arun Banakar
412
add a comment |
up vote
1
down vote
up vote
1
down vote
Check this snippet, this code will change formula to values.
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Select
With sh.UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
Next sh
answered yesterday
Arun Banakar
412
Check this snippet, this code will change formula to values.
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Select
With sh.UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
Next sh
answered yesterday
Arun Banakar
412
answered yesterday
Arun Banakar
412
answered yesterday
Arun Banakar
412
answered yesterday
Arun Banakar
412
412
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53237812%2fsave-as-excel-with-same-format-but-no-formula%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
If i understood your question you can read this post... stackoverflow.com/questions/14981582/… hope this helps
– Ferdinando
yesterday