LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't Copy and Paste or Paste Special between Excel Workbooks wllee Excel Discussion (Misc queries) 5 April 29th 23 03:43 AM
Looping Paste Special Value Transpose AncientLearner Excel Programming 2 January 29th 08 12:18 AM
Automating copy/paste/paste special when row references change Carl LaFong Excel Programming 4 October 8th 07 06:10 AM
help w/ generic copy & paste/paste special routine DavidH[_2_] Excel Programming 5 January 23rd 06 03:58 AM
Dynamic Copy/Paste Special Formulas/Paste Special Values Sharon Perez Excel Programming 3 August 7th 04 09:49 PM


All times are GMT +1. The time now is 06:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"