PasteSpecial(格式和值) [英] PasteSpecial (Format and Value)

问题描述

我有一个创建表的Excel程序/按钮.输入在工作表1上的单行/多列中.输出是相同的，但在工作表2上.两列工作表的标题都相同.在输入表上，其中一个列标题具有一个下拉菜单，而其他列具有公式.当我单击一个按钮时，该行将被复制/粘贴到输出工作表中.我可以通过从下拉框中选择不同的项目并按下按钮以运行程序以填充表2上的表格来重复该过程.问题是我只想复制和粘贴值.更改工作表1上的下拉框会将所有信息更改为工作表2上的同一项目.

I have an excel program/button that creates a table. The inputs are in a single row/multiple columns on sheet 1. The outputs are the same but on a sheet 2. The headings on both sheets of the columns are the same. On the input sheet, one of the column headings has a drop down menu and others have formulas. When I click a button, the row is copied/pasted to the output sheet. I can repeat the process by selecting different items from the drop down box and pushing the button to run the program to populate the table on sheet 2. The problem is the I only want to copy and paste values. Changing the drop down box on sheet 1 changes all of the information on to the same item on sheet 2.

代码如下:

Sub AddHardware()
    Range("b5:m6").Copy Sheets("MTO").Range("B" & rows.Count).End(xlUp).Offset(1, 0)                
End Sub

如何仅粘贴值和格式(不包含公式或下拉列表)?

How do I only paste the values and the formatting (no formulas or drop downs)?

推荐答案

这是一种简单的方法.您可以先进行值设置，然后进行格式设置.我使用了两个变量(也许过大了，是的)，但是它将向您展示如何使用范围来设置复制/目标范围，从长远来看会有所帮助.

Here's a simple way to do that. You can do values, then format. I used two variables (perhaps overkill, yes), but it will show you how you could use ranges to set the copy/destination range which would help in the long run.

Sub copy_val_format()
    Dim copyRng As Range
    Dim destRng As Range
    Set copyRng = Sheets("Sheet1").Range("B5:M6")
    Set destRng = Sheets("MTO").Range("B" & rows.Count).End(xlUp).Offset(1, 0)

    copyRng.Copy
    destRng.PasteSpecial (xlPasteValues)
    copyRng.Copy
    destRng.PasteSpecial (xlPasteFormats)

    Application.CutCopyMode = False
End Sub

或更简单的版本:

Sub simpler()
    Dim lastCell as Range
    Set lastCell = Sheets("MTO").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)

    Sheets("Sheet1").Range("B5:M6").Copy
    lastCell.PasteSpecial (xlPasteValues)

    Sheets("Sheet1").Range("B5:M6").Copy
    lastCell.PasteSpecial (xlPasteFormats)
    Application.CutCopyMode = False
End Sub

这篇关于PasteSpecial(格式和值)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！