CODEWITHSUNDEEP
vbaexcel
Ted
Ted

Reputation: 27

Repeat VBA sub for copy and "paste values" on multiple rows

I am very new to 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.

For example:

(1) Copy--> Paste Values 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)

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

     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

Upvotes: 1

Views: 3133

Answers (1)

rohrl77
rohrl77

Reputation: 3337

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:

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.

Upvotes: 1

Related Questions