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].



enter image description here



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









share|improve this question




















  • 1





    Why use xlPasteValues if 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 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











  • @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
















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].



enter image description here



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









share|improve this question




















  • 1





    Why use xlPasteValues if 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 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











  • @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














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].



enter image description here



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









share|improve this question
















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].



enter image description here



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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 30 at 10:04









Ahmed Ashour

1,3872716




1,3872716










asked Jan 29 at 17:52









Le VuongLe Vuong

1




1








  • 1





    Why use xlPasteValues if 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 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











  • @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





    Why use xlPasteValues if 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 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











  • @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










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


















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














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





















































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







Popular posts from this blog

Сан-Квентин

Алькесар

Josef Freinademetz