使用VBA将值从一个表粘贴到另一个表 [英] Use VBA to paste values from one table to another

问题描述

我有以下VBA代码，该数据表从工作表"Tabled data"中提取一行，复制数据，然后将数据粘贴到工作表"Running list"中的下一个可用行中.但是，原始行具有公式，我需要粘贴值，而不是公式.我已经看到了许多使用Range.PasteSpecial的方法，但是此代码未使用Range，而且我不确定如何将其合并.

I have the following VBA code that takes a single row from Sheet "Tabled data", copies the data, then pastes the data into the next available row in Sheet "Running list". However the original row has formulas and I need the values to paste, not the formulas. I've seen numerous ways to do it with Range.PasteSpecial but this code didn't use Range and I'm not sure how to incorporate it.

注意:我从这里修改了此代码:，则它不起作用，因此我将其保留了.

Note: I modified this code from here: http://msdn.microsoft.com/en-us/library/office/ff837760(v=office.15).aspx. It originally had an IF statement to match content in a cell then paste it in a certain sheet according to the content in the cell. I only had one sheet to copy to and didn't need the IF. I don't really need to find the last row of data to copy either as it will only ever be one row with range of A2:N2. But if I take out the FinalRow section and the For and replace with Range("A2:N2") it doesn't work so I left those in.

有关如何添加PasteValues属性而不使其变得更复杂的任何指导?我也愿意简化For或FinalRow变量，例如使用Range.我只是对VBA有点熟悉，已经做了一些事情，但是通常是在大量搜索和修改代码之后.

Any guidance on how to add in the PasteValues property without making this more complicated? I'm also open to simplification of the For or FinalRow variable such as using Range. I'm only sort of familiar with VBA, having done a few things with it, but usually after much searching and modifying code.

Public Sub CopyData()
Sheets("Tabled data").Select
' Find the last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
 ' Loop through each row
For x = 2 To FinalRow
    ThisValue = Cells(x, 1).Value
    Cells(x, 1).Resize(1, 14).Copy
    Sheets("Running list").Select
    NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
    Cells(NextRow, 1).Select
    ActiveSheet.Paste
    Sheets("Tabled data").Select
Next x
End Sub

推荐答案

只需一次复制所有数据，而不必一次执行一行.

Just copy the data all at once, no need to do it a row at a time.

Sub CopyData()

    With ThisWorkbook.Sheets("Tabled data")
        Dim sourceRange As Range
        Set sourceRange = .Range(.Cells(2, 1), .Cells(getLastRow(.Range("A1").Parent), 14))
    End With

    With ThisWorkbook.Sheets("Running list")
        Dim pasteRow As Long
        Dim pasteRange As Range
        pasteRow = getLastRow(.Range("A1").Parent) + 1
        Set pasteRange = .Range(.Cells(pasteRow, 1), .Cells(pasteRow + sourceRange.Rows.Count, 14))
    End With

    pasteRange.Value = sourceRange.Value

End Sub
Function getLastRow(ws As Worksheet, Optional colNum As Long = 1) As Long

    getLastRow = ws.Cells(ws.Rows.Count, colNum).End(xlUp).Row

End Function

这篇关于使用VBA将值从一个表粘贴到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！