move cell values in excel through vba

Question

I am working on an excel sheet and need to move the same range over and over again to the column "P" + 2

So the next range would be "C15:G15" to "P14". I'm looking for a slimmer solution than to repeat this code and change the ranges for hundreds of times..

ActiveWindow.SmallScroll Down:=-3
Range("C13:G13").Copy
Application.CutCopyMode = False
Selection.Copy
Range("P12").Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
    , SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

score 3 · Accepted Answer · 2015-11-09 16:50:04Z

3

This quick snippet should walk down every second row in column C starting at row 13 till the last populated cell in column C.

Sub move_CG_to_PT()
    Dim rw As Long

    With Worksheets("Sheet4")  '<~~set this worksheet reference properly!
        For rw = 13 To .Cells(.Rows.Count, "C").End(xlUp).Row Step 2
            .Cells(rw - 1, "P").Resize(1, 5) = _
              .Cells(rw, "C").Resize(1, 5).Value
        Next rw
    End With
End Sub

This only transfers the values. If the formatting and/or theme is critical then those could be adjusted for with the following.

Sub move_CG_to_PT_w_Formatting()
    Dim rw As Long

    With Worksheets("Sheet4")  '<~~set this worksheet reference properly!
        For rw = 13 To .Cells(.Rows.Count, "C").End(xlUp).Row Step 2
            .Cells(rw, "C").Resize(1, 5).Copy _
                Destination:=.Cells(rw - 1, "P")
        Next rw
    End With
End Sub

edited Nov 9, 2015 at 16:50

answered Nov 9, 2015 at 16:30

user4039065

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

5 Comments

user4039065 Over a year ago

@R3uK - I typically use Rows.Count when dealing with a worksheet. Too often I am within a limited range specified by a With ... End With statement and need to use .Rows.Count on the truncated range. Keeping the worksheet's row count without the prefix period helps me 'see' the difference. For all intents and purposes, they are the same unless an XLS is being mixed up with an XLSX.

R3uK Over a year ago

Ok, I get it, sorry for the wild edit! ;) I'm more used to load ranges into arrays to make all the tests and then apply changes within the sheet, old habits die hard! ;)

mtorben Over a year ago

Thanks a lot! How do I maintain the source formatting here?

user4039065 Over a year ago

@mtorben - The first routine uses direct value transfer so formatting is not carried across. The second one I added after some thought and the .Copy operation does carry the source formatting along with it.

user4039065 Over a year ago

Be aware that I've corrected a +1 to a -1 in the row for column P in the latter routine.

Collectives™ on Stack Overflow

move cell values in excel through vba

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related