Excel宏：根据条件将行值从一个工作表复制到另一个工作表中的特定位置(Excel Macro: Copying row values from one worksheet to a specific place in another worksheet, based on criteria)

 我现在只在Excel中使用宏大约4个月，并且基本上通过查找现有代码并弄清楚它是如何工作来自学。 我现在有点卡住了。 
 
 我在Excel工作簿中有一个报告。 我需要根据D列中显示的数据将数据复制到多个工作表（在同一工作簿中）。也就是说，我需要复制D列符合某些条件的整行。 原始工作表包含公式，但我只希望在复制数据时显示值。 
 
 我已经能够复制数据，但我有两个问题：1）公式正在复制，而不仅仅是值2）数据出现在单元格A2的新工作表中，但我需要它从单元格A5开始 
 
 我将其设置为模板，因为主报告需要每月运行和拆分，因此我复制的范围不会是恒定的。 这是我目前使用的代码示例： 
 
    Sub RefreshSheets()

    Sheets("ORIGIN").Select
    Dim lr As Long, lr2 As Long, r As Long
    lr = Sheets("ORIGIN").Cells(Rows.Count, "A").End(xlUp).Row
    lr2 = Sheets("DESTINATION").Cells(Rows.Count, "A").End(xlUp).Row

    For r = lr To 2 Step -1
        If Range("D" & r).Value = "movedata" Then
            Rows(r).Copy Destination:=Sheets("DESTINATION").Range("A" & lr2 + 1)
            lr2 = Sheets("DESTINATION").Cells(Rows.Count, "A").End(xlUp).Row
        End If


    Next r

    End Sub
 
 我尝试在“.Range（”A“＆lr2 + 1）”之后添加“.PasteSpecial Paste：= xlPasteValues”，但是我得到了编译错误（预期：语句结束）。 我确信我已经错过了一些显而易见的东西（这是我使用的代码我还没有完全理解的东西），但到目前为止我没有尝试过任何工作。 
 
 任何建议将不胜感激。 

I have only been working with macros in Excel for about 4 months now and have essentially been teaching myself by finding existing code and figuring out how it works. I am now a bit stuck.
 
I have a report in an Excel workbook. I need to copy the data across a number of worksheets (within the same workbook), based on the data that appears in column D. That is to say, I need to copy the entire row where Column D matches certain criteria. The original worksheet contains formulas, but I only want the values to appear when the data is copied. 
 
I have been able to copy the data across, but I have two problems: 1) the formulas are copying across, not just the values 2) the data appears in the new worksheet at cell A2, but I need it to start at cell A5
 
I am setting this up as a template, as the main report needs to be run and split every month, so the range from which I am copying will not be constant. This is a sample of the code I am currently using:
 
    Sub RefreshSheets()

    Sheets("ORIGIN").Select
    Dim lr As Long, lr2 As Long, r As Long
    lr = Sheets("ORIGIN").Cells(Rows.Count, "A").End(xlUp).Row
    lr2 = Sheets("DESTINATION").Cells(Rows.Count, "A").End(xlUp).Row

    For r = lr To 2 Step -1
        If Range("D" & r).Value = "movedata" Then
            Rows(r).Copy Destination:=Sheets("DESTINATION").Range("A" & lr2 + 1)
            lr2 = Sheets("DESTINATION").Cells(Rows.Count, "A").End(xlUp).Row
        End If


    Next r

    End Sub
 
I have tried adding ".PasteSpecial Paste:=xlPasteValues" after ".Range("A" & lr2 + 1)", but I get a compile error (Expected: end of statement). I am sure I have missed something obvious (this is what I get for using code I don't fully understand yet), but nothing I have tried so far has worked. 
 
Any advice would be greatly appreciated.

原文：https://stackoverflow.com/questions/45339522