3

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
Improve this question

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

@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.
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! ;)
Thanks a lot! How do I maintain the source formatting here?
@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.
Be aware that I've corrected a +1 to a -1 in the row for column P in the latter routine.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.