copy cells and insert 2 lines between data

Question

I have to copy Range("A22:A40") form Sheet1 to Sheet3 and Range("A22:A40") from Sheet2 to Sheet3. Each data must be separated by two lines in Sheet3 this is what i did :

With Sheets("Sheet1").Range("A22:A40") 'A40 because i can have maximum 40 data
    Application.Intersect(.SpecialCells(xlCellTypeVisible), _
          .SpecialCells(xlCellTypeConstants)).Copy _
               Destination:=Sheets("Sheet3").Range("A22:A40")
End With

With Sheets("Sheet2").Range("A22:A40") 'A40 because i can have maximum 40 data
    Application.Intersect(.SpecialCells(xlCellTypeVisible), _
          .SpecialCells(xlCellTypeConstants)).Copy _
               Destination:=Sheets("Sheet3").Range("A22:A40")
End With

but Sheets("Sheet3").Range("A22:A40") contains only Sheets ("Sheet2").Range("A22:A40") value .

i want to insert two lines just after and then make the second copy to Sheet3

How can i separeted them by 2 rows ?

MutjayLee MutjayLee · Accepted Answer · 2016-06-08T11:30:31

Since both of the destination is set as Sheets("Sheet3").Range("A22:A40")

You are writing over First one with second one.

Try changing second destination to Sheets("Sheet3").Range("A43:A61")

which will leave two rows between them

copy cells and insert 2 lines between data

1 Answers