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

Question

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

rohrl77 · Accepted Answer · 2015-06-18 06:59:22Z

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:

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.

edited Jun 18, 2015 at 6:59

answered Jun 17, 2015 at 13:56

rohrl77

3,34712 gold badges51 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ted Over a year ago

Thanks for that. How do I repeat this code on the cells below without having to retype all of the ranges (e.g. H4:H5 in the next loop will be H:23:H24). Also, next month I will want to run this on the next column (I4:I5 and so on).

David G Over a year ago

@Ted I don't really see what this does at all. You're copying a range on itself (same sheet, same cell?)

Ted Over a year ago

That's right. It's just a copy and paste task that takes forever because I have formulas in cells H6:H7, etc. and don't want to copy and paste over them with hard coded numbers. And because you can't copy and paste values over multiple cell locations I have to repeat the copy and paste values like 100 times.

rohrl77 Over a year ago

@Ted I can help you do that, but I won't be able to get to it today.

Ted Over a year ago

No worries and no rush. I tried to illustrate it better above. I know this is kind of a dumb task, but it will save me a lot of time and I can apply it to many parts of my spreadsheet. Thanks a lot.

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related