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

grimreeper

New Member
Joined
Feb 12, 2013
Messages
13
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

LloydFinancials

Well-known Member
Joined
Apr 24, 2015
Messages
518
Hello Grim,

See if you can modify this to suit your needs.

Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,707
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Save going to row 1 million plus
Code:
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

New Member
Joined
Feb 12, 2013
Messages
13
Thank you guys. Sorry for the delayed reply. I was facing some connectivity issues. Both codes works like a charm :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top