Copy data from sheet to another sheet in specific way

[YasserKhalil]

Hello experts
I have data in sheet1.range("A1:E" & Last row)
I need simply an effective way to transfer data from sheet1 to sheet2 ..
The transfer process would start in A6 .. each four rows would be transferred then left the next three rows (yellow cells as they are .. note that yellow cells in original file would contain formulas) ... the same with the rest of data
I have attached the expected results

If possible I need to avoid loops as original data is too large
Thanks advanced for help

 

[Deepak]

Hi,

I think without loop yellow cells might get changed.

Sub t1()
Dim i As Double, r As Range
Dim baseRow As Integer: baseRow = 6
Const interval As Integer = 4
Const x As Integer = 3

    With Sheet1.Range("A1").CurrentRegion
        For i = 1 To .Rows.Count Step interval
            With .Cells(i, 1).Resize(interval, .Columns.Count)
                .Copy
                    Sheet2.Cells(baseRow, 1).PasteSpecial xlPasteValues
            End With
            baseRow = baseRow + interval + x
        Next i
    End With

End Sub

 

[YasserKhalil]

Thanks a lot Mr. Deepak for this working and great solution
But i hoped to find solution no need for loops ..
Is it possible to use special cells instead
If we could insert three empty rows in sheet1 ..Is it possible in that case to transfer data in different way?

I mean to copy the whole range in sheet1 and paste over cells in sheet2 skipping yellow cells

 

[Deepak]

Oh! Thanks for pointing the same..

Check this...

Sub test2()
Dim i As Double, s As String, c As Double, lr As Double

Application.ScreenUpdating = False
With Sheet1
c = .[A1].CurrentRegion.Rows.Count
lr = Int((c / 4) * 7)
For i = 4 To c Step 4
    s = s & "," & i + 1 & ":" & i + 3
Next
s = Mid(s, 2)

    .Range(s).Insert Shift:=xlDown
    .Range("A1:E" & lr).Copy
    Sheets("Expected OutPut").Range("A6").PasteSpecial Paste:=xlPasteAll, _
        Operation:=xlNone, SkipBlanks:=True, Transpose:=False
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

 

[YasserKhalil]

Thanks a lot Mr. Deepak for this wonderful solution
For more solution I have posted another thread in this link
http://www.excelforum.com/excel-pro...m-sheet-to-another-sheet-in-specific-way.html

Best and kind regards

 

[shrivallabha]

Thanks a lot Mr. Deepak for this wonderful solution
For more solution I have posted another thread in this link
http://www.excelforum.com/excel-pro...m-sheet-to-another-sheet-in-specific-way.html

Best and kind regards

You have sizable number of posts on the other forum as you have here. You should be aware of cross-posting. Have a read of(Please Don't part):
http://chandoo.org/forum/threads/new-users-please-read.294/

Please read Ken Puls' article on cross-posting here:
http://www.excelguru.ca/content.php?184

 

[YasserKhalil]

Thanks Mr. Shrivallabha for reply
I think it is possible to cross-posting but to insert and put the links of the other thread and I have done this in both thread on both forums ..
And in fact I was in hurry to solve the problem ..
Thanks for the notification

 

[Deepak]

YasserKhalil

Yes! It's good & allowed but you must had to share the link at same time here too..

 

[YasserKhalil]

Thanks Mr. Deepak
Sorry for forgetting to put the link at the same time...
I will take care next time