复制VBA子文件并在多行上“粘贴值”

I am very new to VBA.

我对VBA很陌生。

My financial spreadsheet has become very complex and rife with manual copy and paste tasks that probably could have been setup better initially.

我的财务报表已经变得非常复杂，并且充斥着手工复制和粘贴的任务，这些任务可能本来可以更好地设置。

My task is fairly simple (I think): I have 8 cells I want to copy and paste the values into (setting a dynamic budget in hard-coded values for a previous month), and then repeat this process to 6 other destinations in the same column with the same cell pattern.

我的任务相当简单(我认为):我有8个单元格要复制并粘贴到其中(在前一个月用硬编码值设置一个动态预算)，然后在同一列中使用相同单元格模式对6个其他目的地重复这个过程。

For example:

例如:

(1) Copy--> Paste Values H4:H5, H8, H10 & H13:H16

(1)复制——>粘贴值H4:H5, H8, H10, H13:H16

(2) Repeat on H23:H24 (H4:H5 + 20 rows), H27 (H8 + 20 rows), H39 (H10 + 20 rows), H32:H35 (H13:H:16 + 20 rows)

(2)重复H23:H24 (H4:H5 + 20行)，H27 (H8 + 20行)，H39 (H10 + 20行)，H32:H35 (H13:H:16 + 20行)

(3) Then repeat this same copy and paste pattern many times down the column:

(3)然后重复同样的复制和粘贴模式很多次向下列:

     H
4  **Paste Value**
5  **Paste Value**
6 Leave alone
7 Leave alone
8  **Paste Value**
9 Leave alone
10  **Paste Value**
11 Leave alone
12 Leave alone
13  **Paste Value**
14  **Paste Value**
15  **Paste Value**
16  **Paste Value**

Skip H:17:H22

     H
23 **Paste Value**
24 **Paste Value**
25 Leave alone
26 Leave alone
27  **Paste Value**
28 Leave alone
29  **Paste Value**
30 Leave alone
31 Leave alone
32  **Paste Value**
33  **Paste Value**
34  **Paste Value**
35  **Paste Value**

This is the macro I recorded for the first set:

这是我为第一组录制的宏:

Sub RFC_Paste_Month_Values()
'
' RFC_Paste_Month_Values Macro

    Range("H4:H5").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("H8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("H10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("H13:H16").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

' Copy and "Paste Special- Values" of set budgets '

End Sub

Any help would be much appreciated. Thank you!

如有任何帮助，我们将不胜感激。谢谢你！

Ted

泰德

1 个解决方案

#1

Here is a shortend version of your code with a lot of the macro recorder generated stuff taken out. It can be improved further, but you would need to explain further what it is you are doing:

这里是您的代码的shortend版本，有很多宏记录器生成的东西。它可以进一步改进，但你需要进一步解释你正在做什么:

UPDATED ANSWER

更新后的答案

Sub RFC_Paste_Month_Values()
'
' RFC_Paste_Month_Values Macro
'

Dim i As Integer

With ActiveSheet
    For i = 0 To .UsedRange.Rows.Count Step 19 '
        .Range(.Cells(4 + i, 8), .Cells(5 + i, 8)) = .Range(.Cells(4 + i, 8), .Cells(5 + i, 8)).Value
        .Cells(8 + i, 8) = .Cells(8 + i, 8).Value
        .Cells(10 + i, 8) = .Cells(10 + i, 8).Value
        .Range(.Cells(13 + i, 8), .Cells(16 + i, 8)) = .Range(.Cells(13 + i, 8), .Cells(16 + i, 8)).Value
    Next i
End With

End Sub

Again... I didn't have a chance to test it, but I think it should work for you.

再次……我没有机会测试它，但我认为它应该对你有用。

#1