TRICKY ONE : copy data from rows !!

[wetdrought]

what data i have?
• Row "1" of sheet 1 is full of data in each cell.

what i want to do?
Step 1: I want to copy six colums from row 1 ( i.e. data in cells A1:F1) and paste in sheet 2 row A1:F1.
Step 2: I want to copy next six colums from row 1 ( i.e. data in cells G1:L1) and paste in sheet 2 row A2:F2. and so on till all data from the row "1" is copied.


Please Help!!!

Thanks,
wetdrought.

 

[Gilliam]

Sub WetDrought()
    lastcol = Sheets("Sheet1").Range("A1").End(xlToRight).Column
    For i = 1 To lastcol - 5 Step 6
        Sheets("Sheet1").Range(Cells(1, i), Cells(1, i + 5)).Copy
        With Sheets("Sheet2")
        .Select
        sh2lr = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        .Range("A" & sh2lr).Select
        ActiveSheet.Paste
        End With
        Sheets("Sheet1").Select
    Next i
End Sub

I think this will work for you.

 

[jscranton]

This is not tested:

Sub testFirst()
Dim i
Dim LastColumn
Dim ws As Worksheet, targetSheet As Worksheet


Set ws = ThisWorkbook.Sheets(1)


LastColumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column


For i = 1 To LastColumn


    StartColumn = i


    If StartColumn + 5 <= LastColumn Then
    
        Set targetSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))


        ws.Cells(1, StartColumn).Resize(1, 6).Copy Destination:=targetSheet.Cells(1, 1)
        
        i = i + 5
    
    ElseIf StartColumn + 5 > LastColumn Then
    
        Set targetSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))


        ws.Cells(1, StartColumn).Resize(1, 1 + (LastColumn - StartColumn)).Copy _
        Destination:=targetSheet.Cells(1, 1)
        
        Exit For
        
    End If
Next i
End Sub