Save as Excel with same format, but no formula
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 vba excel-vba excel-formula
edited Nov 12 '18 at 7:19
Pᴇʜ
20.2k42650
asked Nov 10 '18 at 9:55
Santhosh
134
add a comment |
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 vba excel-vba excel-formula
edited Nov 12 '18 at 7:19
Pᴇʜ
20.2k42650
asked Nov 10 '18 at 9:55
Santhosh
134
If i understood your question you can read this post... stackoverflow.com/questions/14981582/… hope this helps
– Ferdinando
Nov 10 '18 at 10:09
add a comment |
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 vba excel-vba excel-formula
edited Nov 12 '18 at 7:19
Pᴇʜ
20.2k42650
asked Nov 10 '18 at 9:55
Santhosh
134
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 vba excel-vba excel-formula
excel vba excel-vba excel-formula
edited Nov 12 '18 at 7:19
Pᴇʜ
20.2k42650
asked Nov 10 '18 at 9:55
Santhosh
134
edited Nov 12 '18 at 7:19
Pᴇʜ
20.2k42650
asked Nov 10 '18 at 9:55
Santhosh
134
edited Nov 12 '18 at 7:19
Pᴇʜ
20.2k42650
edited Nov 12 '18 at 7:19
Pᴇʜ
20.2k42650
edited Nov 12 '18 at 7:19
Pᴇʜ
20.2k42650
20.2k42650
asked Nov 10 '18 at 9:55
Santhosh
134
asked Nov 10 '18 at 9:55
Santhosh
134
asked Nov 10 '18 at 9:55
Santhosh
134
134
If i understood your question you can read this post... stackoverflow.com/questions/14981582/… hope this helps
– Ferdinando
Nov 10 '18 at 10:09
add a comment |
If i understood your question you can read this post... stackoverflow.com/questions/14981582/… hope this helps
– Ferdinando
Nov 10 '18 at 10:09
If i understood your question you can read this post... stackoverflow.com/questions/14981582/… hope this helps
– Ferdinando
Nov 10 '18 at 10:09
If i understood your question you can read this post... stackoverflow.com/questions/14981582/… hope this helps
– Ferdinando
Nov 10 '18 at 10:09
add a comment |
1 Answer
1
active
oldest
votes
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 Nov 10 '18 at 10:44
Arun Banakar
612
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
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
Required, but never shown
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 Nov 10 '18 at 10:44
Arun Banakar
612
add a comment |
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 Nov 10 '18 at 10:44
Arun Banakar
612
add a comment |
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 Nov 10 '18 at 10:44
Arun Banakar
612
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 Nov 10 '18 at 10:44
Arun Banakar
612
answered Nov 10 '18 at 10:44
Arun Banakar
612
answered Nov 10 '18 at 10:44
Arun Banakar
612
answered Nov 10 '18 at 10:44
Arun Banakar
612
612
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
If i understood your question you can read this post... stackoverflow.com/questions/14981582/… hope this helps
– Ferdinando
Nov 10 '18 at 10:09