copy spaced cells to different spacing

  • Thread starter Thread starter aaa
  • Start date Start date
A

aaa

On one tab I have formulae in every 4th row that I want to copy onto another
tab where I want the same formulae every 2nd row. The rows in between these
formulae are not blank. How do I best do this?

Derrick
 
You can use code like the following:

Sub AAA()
Dim Source As Range
Dim Dest As Range
Dim LastCell As Long
Dim WS As Worksheet
Set WS = Worksheets("Sheet1") '<<< source of data
Set Dest = Worksheets("Sheet2").Range("A1") '<<< destination of copy
With WS
LastCell = .Cells(.Rows.Count, "A").End(xlUp).Row
Set Source = .Range("A1") '<<< first row
End With
Do Until Source.Row > LastCell
Source.EntireRow.Copy Destination:=Dest
Set Source = Source.Offset(4, 0) ' down 4 rows
Set Dest = Dest.Offset(2, 0) ' down two rows
Loop
End Sub

Change the lines marked with <<< to values appropriate for your
workbook.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Thanks Chip, that does what I need, only how should the code be different to
limit the selection to be copied to one column instead of the whole row?

Many thanks in advance,
Derrick
 
Back
Top