Excel VBA: paste only value to address from inputbox without loss old format in destination range
0
I want copy from a range and paste the value only to an destination range chosen with inputbox. It works mostly but the old format of destination range is lost [3rd and 4th row differ from 1st row in the attached link of picture below].
What I want is the old format is not lost after paste data from copied range. Is there anything wrong?
I use xlPasteValues and here is the code:
Sub Ttdn_kc_154()
Dim targetRange As Range
Sheets("ps").Select
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If
Sheets("httk").Select
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If
ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=1, Criteria1:="1"
ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=12, Criteria1:="<>0"
If ActiveSheet.Range("httk_lockc1542ps").Value > 0 Then
Range("httk_kcKQKD_datakc").Copy
Set targetRange = Application.InputBox(prompt:="Please input or select a range", Type:=8)
targetRange.PasteSpecial Paste:=xlPasteValues
[the 3rd and 4th row in attached picture link][1]
Application.CutCopyMode = False
End If
microsoft-excel microsoft-excel-2010 vba copy-paste format
edited Jan 30 at 10:04
Ahmed Ashour
1,3251715
asked Jan 29 at 17:52
Le VuongLe Vuong
1
add a comment |
0
I want copy from a range and paste the value only to an destination range chosen with inputbox. It works mostly but the old format of destination range is lost [3rd and 4th row differ from 1st row in the attached link of picture below].
What I want is the old format is not lost after paste data from copied range. Is there anything wrong?
I use xlPasteValues and here is the code:
Sub Ttdn_kc_154()
Dim targetRange As Range
Sheets("ps").Select
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If
Sheets("httk").Select
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If
ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=1, Criteria1:="1"
ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=12, Criteria1:="<>0"
If ActiveSheet.Range("httk_lockc1542ps").Value > 0 Then
Range("httk_kcKQKD_datakc").Copy
Set targetRange = Application.InputBox(prompt:="Please input or select a range", Type:=8)
targetRange.PasteSpecial Paste:=xlPasteValues
[the 3rd and 4th row in attached picture link][1]
Application.CutCopyMode = False
End If
microsoft-excel microsoft-excel-2010 vba copy-paste format
edited Jan 30 at 10:04
Ahmed Ashour
1,3251715
asked Jan 29 at 17:52
Le VuongLe Vuong
1
1
Why usexlPasteValuesif you want to keep the formatting?
– cybernetic.nomad
Jan 29 at 18:35
1
To further @cybernetic.nomad's comment, see this page on pasting values and formats. (Hint: You want to also usexlPasteFormats). And here's the MSDN Page onxlPasteType.
– BruceWayne
Jan 29 at 21:38
@cybernetic.nomad : I use x1Pastevalues because I want: to paste only content of copied cells and maintain the formatting of destination cells after pasting copied cell onto destination cells. And the results of destination cells are not what I want. In the attached picture below, the lost formatting of 4th and 5th rows compare with normal formatting of 1st row i.stack.imgur.com/cfFan.png Sorry, I update more info.
– Le Vuong
Jan 30 at 1:27
@BruceWayne: two pasted rows in destination cells (sheet "ps") are over-pasted the old formatting and border. I am not sure why "targetRange.PasteSpecial Paste:=xlPasteValues" clear formatting and border too. imgur.com/tocxpfY
– Le Vuong
Jan 30 at 7:02
Thank all, It seems change the order of code making everything work: Set targetRange = Application.InputBox(prompt:="Please select a destination range:", Type:=8) Sheets("httk").Select ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=1, Criteria1:="1" ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=12, Criteria1:="<>0" If ActiveSheet.Range("httk_lockc1542ps").Value > 0 Then Range("httk_kcKQKD_datakc").Copy Sheets("ps").Select targetRange.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End If End Sub
– Le Vuong
Jan 30 at 8:00
add a comment |
0
0
0
I want copy from a range and paste the value only to an destination range chosen with inputbox. It works mostly but the old format of destination range is lost [3rd and 4th row differ from 1st row in the attached link of picture below].
What I want is the old format is not lost after paste data from copied range. Is there anything wrong?
I use xlPasteValues and here is the code:
Sub Ttdn_kc_154()
Dim targetRange As Range
Sheets("ps").Select
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If
Sheets("httk").Select
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If
ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=1, Criteria1:="1"
ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=12, Criteria1:="<>0"
If ActiveSheet.Range("httk_lockc1542ps").Value > 0 Then
Range("httk_kcKQKD_datakc").Copy
Set targetRange = Application.InputBox(prompt:="Please input or select a range", Type:=8)
targetRange.PasteSpecial Paste:=xlPasteValues
[the 3rd and 4th row in attached picture link][1]
Application.CutCopyMode = False
End If
microsoft-excel microsoft-excel-2010 vba copy-paste format
edited Jan 30 at 10:04
Ahmed Ashour
1,3251715
asked Jan 29 at 17:52
Le VuongLe Vuong
1
I want copy from a range and paste the value only to an destination range chosen with inputbox. It works mostly but the old format of destination range is lost [3rd and 4th row differ from 1st row in the attached link of picture below].
What I want is the old format is not lost after paste data from copied range. Is there anything wrong?
I use xlPasteValues and here is the code:
Sub Ttdn_kc_154()
Dim targetRange As Range
Sheets("ps").Select
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If
Sheets("httk").Select
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If
ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=1, Criteria1:="1"
ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=12, Criteria1:="<>0"
If ActiveSheet.Range("httk_lockc1542ps").Value > 0 Then
Range("httk_kcKQKD_datakc").Copy
Set targetRange = Application.InputBox(prompt:="Please input or select a range", Type:=8)
targetRange.PasteSpecial Paste:=xlPasteValues
[the 3rd and 4th row in attached picture link][1]
Application.CutCopyMode = False
End If
microsoft-excel microsoft-excel-2010 vba copy-paste format
microsoft-excel microsoft-excel-2010 vba copy-paste format
edited Jan 30 at 10:04
Ahmed Ashour
1,3251715
asked Jan 29 at 17:52
Le VuongLe Vuong
1
edited Jan 30 at 10:04
Ahmed Ashour
1,3251715
asked Jan 29 at 17:52
Le VuongLe Vuong
1
edited Jan 30 at 10:04
Ahmed Ashour
1,3251715
edited Jan 30 at 10:04
Ahmed Ashour
1,3251715
edited Jan 30 at 10:04
Ahmed Ashour
1,3251715
1,3251715
asked Jan 29 at 17:52
Le VuongLe Vuong
1
asked Jan 29 at 17:52
Le VuongLe Vuong
1
asked Jan 29 at 17:52
Le VuongLe Vuong
1
1
1
Why usexlPasteValuesif you want to keep the formatting?
– cybernetic.nomad
Jan 29 at 18:35
1
To further @cybernetic.nomad's comment, see this page on pasting values and formats. (Hint: You want to also usexlPasteFormats). And here's the MSDN Page onxlPasteType.
– BruceWayne
Jan 29 at 21:38
@cybernetic.nomad : I use x1Pastevalues because I want: to paste only content of copied cells and maintain the formatting of destination cells after pasting copied cell onto destination cells. And the results of destination cells are not what I want. In the attached picture below, the lost formatting of 4th and 5th rows compare with normal formatting of 1st row i.stack.imgur.com/cfFan.png Sorry, I update more info.
– Le Vuong
Jan 30 at 1:27
@BruceWayne: two pasted rows in destination cells (sheet "ps") are over-pasted the old formatting and border. I am not sure why "targetRange.PasteSpecial Paste:=xlPasteValues" clear formatting and border too. imgur.com/tocxpfY
– Le Vuong
Jan 30 at 7:02
Thank all, It seems change the order of code making everything work: Set targetRange = Application.InputBox(prompt:="Please select a destination range:", Type:=8) Sheets("httk").Select ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=1, Criteria1:="1" ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=12, Criteria1:="<>0" If ActiveSheet.Range("httk_lockc1542ps").Value > 0 Then Range("httk_kcKQKD_datakc").Copy Sheets("ps").Select targetRange.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End If End Sub
– Le Vuong
Jan 30 at 8:00
add a comment |
1
Why usexlPasteValuesif you want to keep the formatting?
– cybernetic.nomad
Jan 29 at 18:35
1
To further @cybernetic.nomad's comment, see this page on pasting values and formats. (Hint: You want to also usexlPasteFormats). And here's the MSDN Page onxlPasteType.
– BruceWayne
Jan 29 at 21:38
@cybernetic.nomad : I use x1Pastevalues because I want: to paste only content of copied cells and maintain the formatting of destination cells after pasting copied cell onto destination cells. And the results of destination cells are not what I want. In the attached picture below, the lost formatting of 4th and 5th rows compare with normal formatting of 1st row i.stack.imgur.com/cfFan.png Sorry, I update more info.
– Le Vuong
Jan 30 at 1:27
@BruceWayne: two pasted rows in destination cells (sheet "ps") are over-pasted the old formatting and border. I am not sure why "targetRange.PasteSpecial Paste:=xlPasteValues" clear formatting and border too. imgur.com/tocxpfY
– Le Vuong
Jan 30 at 7:02
Thank all, It seems change the order of code making everything work: Set targetRange = Application.InputBox(prompt:="Please select a destination range:", Type:=8) Sheets("httk").Select ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=1, Criteria1:="1" ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=12, Criteria1:="<>0" If ActiveSheet.Range("httk_lockc1542ps").Value > 0 Then Range("httk_kcKQKD_datakc").Copy Sheets("ps").Select targetRange.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End If End Sub
– Le Vuong
Jan 30 at 8:00
1
1
Why use
xlPasteValues if you want to keep the formatting?– cybernetic.nomad
Jan 29 at 18:35
Why use
xlPasteValues if you want to keep the formatting?– cybernetic.nomad
Jan 29 at 18:35
1
1
To further @cybernetic.nomad's comment, see this page on pasting values and formats. (Hint: You want to also use
xlPasteFormats). And here's the MSDN Page on xlPasteType.– BruceWayne
Jan 29 at 21:38
To further @cybernetic.nomad's comment, see this page on pasting values and formats. (Hint: You want to also use
xlPasteFormats). And here's the MSDN Page on xlPasteType.– BruceWayne
Jan 29 at 21:38
@cybernetic.nomad : I use x1Pastevalues because I want: to paste only content of copied cells and maintain the formatting of destination cells after pasting copied cell onto destination cells. And the results of destination cells are not what I want. In the attached picture below, the lost formatting of 4th and 5th rows compare with normal formatting of 1st row i.stack.imgur.com/cfFan.png Sorry, I update more info.
– Le Vuong
Jan 30 at 1:27
@cybernetic.nomad : I use x1Pastevalues because I want: to paste only content of copied cells and maintain the formatting of destination cells after pasting copied cell onto destination cells. And the results of destination cells are not what I want. In the attached picture below, the lost formatting of 4th and 5th rows compare with normal formatting of 1st row i.stack.imgur.com/cfFan.png Sorry, I update more info.
– Le Vuong
Jan 30 at 1:27
@BruceWayne: two pasted rows in destination cells (sheet "ps") are over-pasted the old formatting and border. I am not sure why "targetRange.PasteSpecial Paste:=xlPasteValues" clear formatting and border too. imgur.com/tocxpfY
– Le Vuong
Jan 30 at 7:02
@BruceWayne: two pasted rows in destination cells (sheet "ps") are over-pasted the old formatting and border. I am not sure why "targetRange.PasteSpecial Paste:=xlPasteValues" clear formatting and border too. imgur.com/tocxpfY
– Le Vuong
Jan 30 at 7:02
Thank all, It seems change the order of code making everything work: Set targetRange = Application.InputBox(prompt:="Please select a destination range:", Type:=8) Sheets("httk").Select ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=1, Criteria1:="1" ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=12, Criteria1:="<>0" If ActiveSheet.Range("httk_lockc1542ps").Value > 0 Then Range("httk_kcKQKD_datakc").Copy Sheets("ps").Select targetRange.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End If End Sub
– Le Vuong
Jan 30 at 8:00
Thank all, It seems change the order of code making everything work: Set targetRange = Application.InputBox(prompt:="Please select a destination range:", Type:=8) Sheets("httk").Select ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=1, Criteria1:="1" ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=12, Criteria1:="<>0" If ActiveSheet.Range("httk_lockc1542ps").Value > 0 Then Range("httk_kcKQKD_datakc").Copy Sheets("ps").Select targetRange.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End If End Sub
– Le Vuong
Jan 30 at 8:00
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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
});
}
});
draft saved
draft discarded
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%2fsuperuser.com%2fquestions%2f1399752%2fexcel-vba-paste-only-value-to-address-from-inputbox-without-loss-old-format-in%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Super User!
- 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.
draft saved
draft discarded
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%2fsuperuser.com%2fquestions%2f1399752%2fexcel-vba-paste-only-value-to-address-from-inputbox-without-loss-old-format-in%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
1
Why use
xlPasteValuesif you want to keep the formatting?– cybernetic.nomad
Jan 29 at 18:35
1
To further @cybernetic.nomad's comment, see this page on pasting values and formats. (Hint: You want to also use
xlPasteFormats). And here's the MSDN Page onxlPasteType.– BruceWayne
Jan 29 at 21:38
@cybernetic.nomad : I use x1Pastevalues because I want: to paste only content of copied cells and maintain the formatting of destination cells after pasting copied cell onto destination cells. And the results of destination cells are not what I want. In the attached picture below, the lost formatting of 4th and 5th rows compare with normal formatting of 1st row i.stack.imgur.com/cfFan.png Sorry, I update more info.
– Le Vuong
Jan 30 at 1:27
@BruceWayne: two pasted rows in destination cells (sheet "ps") are over-pasted the old formatting and border. I am not sure why "targetRange.PasteSpecial Paste:=xlPasteValues" clear formatting and border too. imgur.com/tocxpfY
– Le Vuong
Jan 30 at 7:02
Thank all, It seems change the order of code making everything work: Set targetRange = Application.InputBox(prompt:="Please select a destination range:", Type:=8) Sheets("httk").Select ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=1, Criteria1:="1" ActiveSheet.Range("httk_kcKQKD_filter").AutoFilter Field:=12, Criteria1:="<>0" If ActiveSheet.Range("httk_lockc1542ps").Value > 0 Then Range("httk_kcKQKD_datakc").Copy Sheets("ps").Select targetRange.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End If End Sub
– Le Vuong
Jan 30 at 8:00