Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Every Nth Row to Copy & Paste Special
I have some code that goes through a range (for example, D1:H10) and for
every 3rd row, it highlights D3:H3, copies it and then moves up one row to D2 and paste specials the value. I'm trying to alter a piece of code I used to insert the formula in the same cell range that I am copying and pasting, but I'm missing how to prevent it from not selecting every 3rd row in the range as it loops. Here's the code: -----Begin Code----- Dim ColsSelection Dim RowsSelection Dim RowsBetween Dim Diff Dim FinalRange Dim xCell Range("D1:H10").Select ColsSelection = Selection.Columns.Count RowsSelection = Selection.Rows.Count RowsBetween = 3 Diff = Selection.Row - 1 Selection.Resize(RowsSelection, 1).Select Set FinalRange = Selection. _ Offset(RowsBetween - 1, 0).Resize(1, ColsSelection) For Each xCell In Selection If xCell.Row Mod RowsBetween = Diff Then Set FinalRange = Application.Union _ (FinalRange, xCell.Resize(1, ColsSelection)) FinalRange.Select Selection.Copy ActiveCell.Offset(-1, 0).Select Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Selection.NumberFormat = "0" End If Next xCell Range("A2").Activate End Sub -----End Code----- Specifically, this piece to the code: Set FinalRange = Application.Union(FinalRange, xCell.Resize(1, ColsSelection)) Tells it to keep adding to the selected range, but I don't want it to keep adding to the selected range. I want it to move down 4 rows and then select D6:H6 and copy and paste special value again. I tried commenting out the Union code, but then it keeps selecting D3:H3 through each loop. Any suggestions as to how I can get it to stop adding to the selected range? Thanks for any help. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Looping Paste Special Value Transpose | Excel Programming | |||
Automating copy/paste/paste special when row references change | Excel Programming | |||
help w/ generic copy & paste/paste special routine | Excel Programming | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming |