VBA to copy data from text upto 50k rows and paste in new sheet in same workbook.

[grimreeper]

Hello All,

I need to copy data from a text file which has a million rows into excel.

The vba code should copy only 50k rows from text file at once into 20 separate sheets in excel workbook so that each sheet has only 50k rows in excel. Any help would be really appreciated.

Thanks,

Grim

 

[LloydFinancials]

Hello Grim,

See if you can modify this to suit your needs.

Sub Lime()On Error Resume Next
Dim srcSheet As String
Dim RngLen As Long
RngLen = 50000 'Change to suit
srcSheet = "Sheet1" 'Change to suit
For x = 1 To 1048576 Step RngLen
Sheets(srcSheet).Range("A" & x & ":A" & x + RngLen - 1).Resize(, 25).Copy
Worksheets.Add After:=Worksheets(srcSheet)
ActiveSheet.Range("A1").PasteSpecial
Next
End Sub

Luke

 

[Michael M]

Save going to row 1 million plus

Sub Lime()
Dim srcSheet As String, RngLen As Long, lr As Long
On Error Resume Next
lr = Cells(Rows.Count, "A").End(xlUp).Row
RngLen = 50000 'Change to suit
srcSheet = "Sheet1" 'Change to suit
    For x = 1 To lr Step RngLen
        Sheets(srcSheet).Range("A" & x & ":A" & x + RngLen - 1).Resize(, 25).Copy
        Worksheets.Add After:=Worksheets(srcSheet)
        ActiveSheet.Range("A1").PasteSpecial
    Next
End Sub

 

[grimreeper]

Thank you guys. Sorry for the delayed reply. I was facing some connectivity issues. Both codes works like a charm