Solved: For loop for Large Data

[anandbohra]

Hi All
every time i stuck in For loop only pl help
I have Large data of 6000 Rows by 23 Columns
which i want to convert into 138000 rows by transposing one below other with condition if row exceed 60000 then jump to next sheet

eg. Sheet 1 contains my data matrix 6000 rows * 23 columns
now copy row 1 paste it as transpose in Sheet 2 (now it has 23 rows)
then again go to sheet 1 copy row 2 again paste it as transpose in Sheet2 at the end ( now it has 46 rows)
repeat this proceedure till total rows reaches 60000 if it exceeds 60000 then add new sheet & continue the proceedure till last row in Sheet 1 (i.e. till row 6000)


PL help

[JimmyTheHand]

Sub test()
Dim Src As Range, Tgt As Range, c As Range
Dim Wbk As Workbook, Ws As Worksheet, WsX As Long

Set Wbk = ThisWorkbook
Set Src = Wbk.Sheets(1).Range("A1:A6000")
Set Ws = Wbk.Sheets.Add
Set Tgt = Ws.Range("A1")

For Each c In Src
c.Resize(, 23).Copy
Tgt.PasteSpecial xlPasteAll, , , True
Set Tgt = Tgt.Offset(23)
If Tgt.Row > 60000 Then
With Ws.Range("A1:A60000")
.Copy
Wbk.Sheets.Add
ActiveSheet.Paste
.EntireRow.Delete
End With
End If
Next
End Sub

HTH

Jimmy

[anandbohra]

Thanks you Very Much

:clap: :clap: :clap: :clap: :clap:


:thumb