Duplicating Excel sheet as values/pictures without references to other worksheet
I am trying to create a macro that dublicates and renames a dashboard, but doesn't include references to other worksheets. So basically i need it to insert charts as "pictures" and cells as values.
Until now, I have finished the duplication, but it still refers back to other worksheets and hence change correspondingly to the original dashboard.
Here is my code so far:
Sub CopySheet()
Dim i As Integer, x As Integer
Dim shtname As String
i = Application.InputBox("How many copies of this dashboard do you need?", "Copy sheet", Type:=1)
For x = 0 To i - 1
Worksheets("Dashboard").Copy After:=Sheets(Sheets.Count)
shtname = InputBox("What do you want to name your new dashboard?")
ActiveSheet.Name = shtname
Next x
End Sub
excel vba charts reference copy
edited Nov 16 '18 at 11:01
Pᴇʜ
20.2k42650
asked Nov 16 '18 at 9:39
Rasmus Engelbrecht Sørensen
183
add a comment |
I am trying to create a macro that dublicates and renames a dashboard, but doesn't include references to other worksheets. So basically i need it to insert charts as "pictures" and cells as values.
Until now, I have finished the duplication, but it still refers back to other worksheets and hence change correspondingly to the original dashboard.
Here is my code so far:
Sub CopySheet()
Dim i As Integer, x As Integer
Dim shtname As String
i = Application.InputBox("How many copies of this dashboard do you need?", "Copy sheet", Type:=1)
For x = 0 To i - 1
Worksheets("Dashboard").Copy After:=Sheets(Sheets.Count)
shtname = InputBox("What do you want to name your new dashboard?")
ActiveSheet.Name = shtname
Next x
End Sub
excel vba charts reference copy
edited Nov 16 '18 at 11:01
Pᴇʜ
20.2k42650
asked Nov 16 '18 at 9:39
Rasmus Engelbrecht Sørensen
183
add a comment |
I am trying to create a macro that dublicates and renames a dashboard, but doesn't include references to other worksheets. So basically i need it to insert charts as "pictures" and cells as values.
Until now, I have finished the duplication, but it still refers back to other worksheets and hence change correspondingly to the original dashboard.
Here is my code so far:
Sub CopySheet()
Dim i As Integer, x As Integer
Dim shtname As String
i = Application.InputBox("How many copies of this dashboard do you need?", "Copy sheet", Type:=1)
For x = 0 To i - 1
Worksheets("Dashboard").Copy After:=Sheets(Sheets.Count)
shtname = InputBox("What do you want to name your new dashboard?")
ActiveSheet.Name = shtname
Next x
End Sub
excel vba charts reference copy
edited Nov 16 '18 at 11:01
Pᴇʜ
20.2k42650
asked Nov 16 '18 at 9:39
Rasmus Engelbrecht Sørensen
183
I am trying to create a macro that dublicates and renames a dashboard, but doesn't include references to other worksheets. So basically i need it to insert charts as "pictures" and cells as values.
Until now, I have finished the duplication, but it still refers back to other worksheets and hence change correspondingly to the original dashboard.
Here is my code so far:
Sub CopySheet()
Dim i As Integer, x As Integer
Dim shtname As String
i = Application.InputBox("How many copies of this dashboard do you need?", "Copy sheet", Type:=1)
For x = 0 To i - 1
Worksheets("Dashboard").Copy After:=Sheets(Sheets.Count)
shtname = InputBox("What do you want to name your new dashboard?")
ActiveSheet.Name = shtname
Next x
End Sub
excel vba charts reference copy
excel vba charts reference copy
edited Nov 16 '18 at 11:01
Pᴇʜ
20.2k42650
asked Nov 16 '18 at 9:39
Rasmus Engelbrecht Sørensen
183
edited Nov 16 '18 at 11:01
Pᴇʜ
20.2k42650
asked Nov 16 '18 at 9:39
Rasmus Engelbrecht Sørensen
183
edited Nov 16 '18 at 11:01
Pᴇʜ
20.2k42650
edited Nov 16 '18 at 11:01
Pᴇʜ
20.2k42650
edited Nov 16 '18 at 11:01
Pᴇʜ
20.2k42650
20.2k42650
asked Nov 16 '18 at 9:39
Rasmus Engelbrecht Sørensen
183
asked Nov 16 '18 at 9:39
Rasmus Engelbrecht Sørensen
183
asked Nov 16 '18 at 9:39
Rasmus Engelbrecht Sørensen
183
183
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
if i understood your question you can try this example code where you get an picture as image and value without formula:
sub test()
Dim sPath As String, sFile As String
Dim wb As Workbook
sPath = "yourPath"
sFile = sPath & "yuorFile.xlsx"
Set wb = Workbooks.Open(sFile)
Range("A1:B8").Select ' select my value range
Selection.Copy 'copy it
Windows("NameFileDestination").Activate 'destination copy value
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("yuorFile.xlsx").Activate 'active source file where there is the chart
ActiveSheet.ChartObjects("Graphic name").Activate
Application.CutCopyMode = False
ActiveChart.ChartArea.Copy ' copy chart like image
Windows("NameFileDestination").Activate
Range("D2").Select
'below there is in italian immagine change in image
ActiveSheet.PasteSpecial Format:="Immagine (PNG)", Link:=False, _
DisplayAsIcon:=False
ActiveWorkbook.Save
wb.Close
end sub
Hope this helps
answered Nov 18 '18 at 22:29
Ferdinando
536312
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%2f53335076%2fduplicating-excel-sheet-as-values-pictures-without-references-to-other-worksheet%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
if i understood your question you can try this example code where you get an picture as image and value without formula:
sub test()
Dim sPath As String, sFile As String
Dim wb As Workbook
sPath = "yourPath"
sFile = sPath & "yuorFile.xlsx"
Set wb = Workbooks.Open(sFile)
Range("A1:B8").Select ' select my value range
Selection.Copy 'copy it
Windows("NameFileDestination").Activate 'destination copy value
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("yuorFile.xlsx").Activate 'active source file where there is the chart
ActiveSheet.ChartObjects("Graphic name").Activate
Application.CutCopyMode = False
ActiveChart.ChartArea.Copy ' copy chart like image
Windows("NameFileDestination").Activate
Range("D2").Select
'below there is in italian immagine change in image
ActiveSheet.PasteSpecial Format:="Immagine (PNG)", Link:=False, _
DisplayAsIcon:=False
ActiveWorkbook.Save
wb.Close
end sub
Hope this helps
answered Nov 18 '18 at 22:29
Ferdinando
536312
add a comment |
if i understood your question you can try this example code where you get an picture as image and value without formula:
sub test()
Dim sPath As String, sFile As String
Dim wb As Workbook
sPath = "yourPath"
sFile = sPath & "yuorFile.xlsx"
Set wb = Workbooks.Open(sFile)
Range("A1:B8").Select ' select my value range
Selection.Copy 'copy it
Windows("NameFileDestination").Activate 'destination copy value
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("yuorFile.xlsx").Activate 'active source file where there is the chart
ActiveSheet.ChartObjects("Graphic name").Activate
Application.CutCopyMode = False
ActiveChart.ChartArea.Copy ' copy chart like image
Windows("NameFileDestination").Activate
Range("D2").Select
'below there is in italian immagine change in image
ActiveSheet.PasteSpecial Format:="Immagine (PNG)", Link:=False, _
DisplayAsIcon:=False
ActiveWorkbook.Save
wb.Close
end sub
Hope this helps
answered Nov 18 '18 at 22:29
Ferdinando
536312
add a comment |
if i understood your question you can try this example code where you get an picture as image and value without formula:
sub test()
Dim sPath As String, sFile As String
Dim wb As Workbook
sPath = "yourPath"
sFile = sPath & "yuorFile.xlsx"
Set wb = Workbooks.Open(sFile)
Range("A1:B8").Select ' select my value range
Selection.Copy 'copy it
Windows("NameFileDestination").Activate 'destination copy value
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("yuorFile.xlsx").Activate 'active source file where there is the chart
ActiveSheet.ChartObjects("Graphic name").Activate
Application.CutCopyMode = False
ActiveChart.ChartArea.Copy ' copy chart like image
Windows("NameFileDestination").Activate
Range("D2").Select
'below there is in italian immagine change in image
ActiveSheet.PasteSpecial Format:="Immagine (PNG)", Link:=False, _
DisplayAsIcon:=False
ActiveWorkbook.Save
wb.Close
end sub
Hope this helps
answered Nov 18 '18 at 22:29
Ferdinando
536312
if i understood your question you can try this example code where you get an picture as image and value without formula:
sub test()
Dim sPath As String, sFile As String
Dim wb As Workbook
sPath = "yourPath"
sFile = sPath & "yuorFile.xlsx"
Set wb = Workbooks.Open(sFile)
Range("A1:B8").Select ' select my value range
Selection.Copy 'copy it
Windows("NameFileDestination").Activate 'destination copy value
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("yuorFile.xlsx").Activate 'active source file where there is the chart
ActiveSheet.ChartObjects("Graphic name").Activate
Application.CutCopyMode = False
ActiveChart.ChartArea.Copy ' copy chart like image
Windows("NameFileDestination").Activate
Range("D2").Select
'below there is in italian immagine change in image
ActiveSheet.PasteSpecial Format:="Immagine (PNG)", Link:=False, _
DisplayAsIcon:=False
ActiveWorkbook.Save
wb.Close
end sub
Hope this helps
answered Nov 18 '18 at 22:29
Ferdinando
536312
answered Nov 18 '18 at 22:29
Ferdinando
536312
answered Nov 18 '18 at 22:29
Ferdinando
536312
answered Nov 18 '18 at 22:29
Ferdinando
536312
536312
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%2f53335076%2fduplicating-excel-sheet-as-values-pictures-without-references-to-other-worksheet%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
