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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

LloydFinancials

Well-known Member
Joined
Apr 24, 2015
Messages
537
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
21,282
Office Version
  1. 365
  2. 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 :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,150
Messages
5,835,685
Members
430,376
Latest member
eeehhhyyy

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