Macro to space 6 rows between data

[howard]

I have a macro to copy data from Column P on sheet2 to Column A on Sheet3

I need code that will insert 6 rows between each item in Col A on Sheet3.


I tried to write code to do this, but cannot get it to work

 Sub Insert6Rows()
Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row

With Range("A1:A" & LR)
.EntireRow.Offset(6).Insert
End With

End Sub

It would be appreciated if someone could kindly provide me with the code

 

[mumps]

Can you post the code you are using to copy/paste the data?

 

[jproffer]

It would be easier to just put the data 6 rows down than to insert 6 rows after the fact. What code are you using to pull the data now?

EDIT: I was too slow ...looks like we're thinking the same thing.

 

[howard]

Hi Mumps

Thanks for the reply

 Sub Copy_Branch_Names()
  
   
    
  With Sheets(2)
  Dim LR As Long
LR = .Cells(.Rows.Count, "P").End(xlUp).Row
    
     .Range("P1:P" & LR).Copy Destination:=Sheets(3).Range("A1")

    End With
    
    End Sub

 

[Fluff]

How about

Sub copyData()
   Dim ary As Variant
   Dim i As Long, j As Long
   
   j = 1
   ary = Sheets(2).Range("P1", Sheets(2).Range("P" & Rows.Count).End(xlUp))
      With Sheets(3)
         For i = 1 To UBound(ary)
            .Range("A" & j).Value = ary(i, 1)
            j = j + 7
         Next i
      End With
End Sub

 

[howard]

Thanks for the help Fluff. Your code works perfectly

 

[Fluff]

Glad we could help & thanks for the feedback