Copying 300 rows at a time from one column with 3K rows and convert to csv file

  • I have a one-column result that have an alphanumeric data with 3k rows. We have an application that has a limitation of processing 300 rows only, and that means we will have 10 workbooks. We want to save each workbook as a separate csv file so it can be processed much faster. Can someone help how we can start this?

  • Here is something, check out the sample, and change the folder location to save the csv files, also change the ranges.


    Note: I forgot to assign the button to the new macro name



    TestOne.xlsm

  • Dave,


    I ran your program and it worked perfectly fine, even the saving of the csv file. However, when I substituted my own variables, I was getting a lot of errors. The ones I underlined were the ones that gave me the most trouble. I only have one column of 3K plus rows. Your VBA code is written cleanly. Thanks in advance and for making the time from your schedule to work on this little problem.


    Sub Something_Or_Other()
    Dim LstRw As Long, sh As Worksheet, ws As Worksheet, x

    Set ws = Sheets("Start")
    Application.DisplayAlerts = False
    For x = 1 To 10
    Set sh = Sheets.Add
    sh.Name = ???
    With ws
    ActiveSheet.Range("F2:F3000").Value = .Range("F2:F3000").Value
    .Range("F2:F3000").EntireRow.Delete shift:=xlUp

    Application.CutCopyMode = False
    End With
    Sheets(x).Copy
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\bx8qd0\Downloads\TextCSV" & x & ".csv", FileFormat:=xlCSV, _
    CreateBackup:=False
    ActiveWorkbook.Close
    Next x


    End Sub

  • Dave,
    Your code works brilliantly except for the saved values on the C: folder. Each 10 folders have the same contents of 300 items each.

  • AH, I should have had `sh.copy` not sheets(x).copy
    sheets(x) would always be the same sheet

  • I made program in which print part giving error
    if possible please provide solution.


    Sub slipfile()


    Dim A,B,C,D,E,FF,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z As Variant
    Dim AA,AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP,AQ,AR,AAS,AT,AU,AV,AW,AX,AY,AZ As Variant
    Dim BA,BB,BC,BD,BE,BF,BG,BH,BI,BJ,BK,BL,BM,BN,BO,BP,BQ,BR,BS,BT,BU,BV,BW,BX,BBY,BZ As Variant
    Dim CA,CB,CC,CD,CE,CF,CG,CH,CI,CJ,CK,CL,CM,CN,CO,CP,CQ,CR,CS,CT,CU,CV,CW,CX,CY,CZ As Variant
    Dim DA,DB,DC,DD,DE,DF,DG,DH,DI,DJ,DK,DL,DM,DN,DDO,DP,DQ,DR,DS,DT,DU,DV,DW,DX,DY,DZ As Variant

    Dim no As Long, fi As Long
    Const cnst = 500, f As String = "D:"
    fi = FreeFile
    With ActiveSheet


    A= .Range("A1:A" & CStr(.Cells(.Rows.Count,"A").End(xlUp).Row)).Value2
    B= .Range("B1:B" & CStr(.Cells(.Rows.Count,"B").End(xlUp).Row)).Value2
    C= .Range("C1:C" & CStr(.Cells(.Rows.Count,"C").End(xlUp).Row)).Value2
    D= .Range("D1:D" & CStr(.Cells(.Rows.Count,"D").End(xlUp).Row)).Value2
    E= .Range("E1:E" & CStr(.Cells(.Rows.Count,"E").End(xlUp).Row)).Value2
    FF= .Range("F1:F" & CStr(.Cells(.Rows.Count,"F").End(xlUp).Row)).Value2
    G= .Range("G1:G" & CStr(.Cells(.Rows.Count,"G").End(xlUp).Row)).Value2
    H= .Range("H1:H" & CStr(.Cells(.Rows.Count,"H").End(xlUp).Row)).Value2
    I= .Range("I1:I" & CStr(.Cells(.Rows.Count,"I").End(xlUp).Row)).Value2
    J= .Range("J1:J" & CStr(.Cells(.Rows.Count,"J").End(xlUp).Row)).Value2
    K= .Range("K1:K" & CStr(.Cells(.Rows.Count,"K").End(xlUp).Row)).Value2
    L= .Range("L1:L" & CStr(.Cells(.Rows.Count,"L").End(xlUp).Row)).Value2
    M= .Range("M1:M" & CStr(.Cells(.Rows.Count,"M").End(xlUp).Row)).Value2
    N= .Range("N1:N" & CStr(.Cells(.Rows.Count,"N").End(xlUp).Row)).Value2
    O= .Range("O1:O" & CStr(.Cells(.Rows.Count,"O").End(xlUp).Row)).Value2
    P= .Range("P1:P" & CStr(.Cells(.Rows.Count,"P").End(xlUp).Row)).Value2
    Q= .Range("Q1:Q" & CStr(.Cells(.Rows.Count,"Q").End(xlUp).Row)).Value2
    R= .Range("R1:R" & CStr(.Cells(.Rows.Count,"R").End(xlUp).Row)).Value2
    S= .Range("S1:S" & CStr(.Cells(.Rows.Count,"S").End(xlUp).Row)).Value2
    T= .Range("T1:T" & CStr(.Cells(.Rows.Count,"T").End(xlUp).Row)).Value2
    U= .Range("U1:U" & CStr(.Cells(.Rows.Count,"U").End(xlUp).Row)).Value2
    V= .Range("V1:V" & CStr(.Cells(.Rows.Count,"V").End(xlUp).Row)).Value2
    W= .Range("W1:W" & CStr(.Cells(.Rows.Count,"W").End(xlUp).Row)).Value2
    X= .Range("X1:X" & CStr(.Cells(.Rows.Count,"X").End(xlUp).Row)).Value2
    Y= .Range("Y1:Y" & CStr(.Cells(.Rows.Count,"Y").End(xlUp).Row)).Value2
    Z= .Range("Z1:Z" & CStr(.Cells(.Rows.Count,"Z").End(xlUp).Row)).Value2
    AA= .Range("AA1:AA" & CStr(.Cells(.Rows.Count,"AA").End(xlUp).Row)).Value2
    AB= .Range("AB1:AB" & CStr(.Cells(.Rows.Count,"AB").End(xlUp).Row)).Value2
    AC= .Range("AC1:AC" & CStr(.Cells(.Rows.Count,"AC").End(xlUp).Row)).Value2
    AD= .Range("AD1:AD" & CStr(.Cells(.Rows.Count,"AD").End(xlUp).Row)).Value2
    AE= .Range("AE1:AE" & CStr(.Cells(.Rows.Count,"AE").End(xlUp).Row)).Value2
    AF= .Range("AF1:AF" & CStr(.Cells(.Rows.Count,"AF").End(xlUp).Row)).Value2
    AG= .Range("AG1:AG" & CStr(.Cells(.Rows.Count,"AG").End(xlUp).Row)).Value2
    AH= .Range("AH1:AH" & CStr(.Cells(.Rows.Count,"AH").End(xlUp).Row)).Value2
    AI= .Range("AI1:AI" & CStr(.Cells(.Rows.Count,"AI").End(xlUp).Row)).Value2
    AJ= .Range("AJ1:AJ" & CStr(.Cells(.Rows.Count,"AJ").End(xlUp).Row)).Value2
    AK= .Range("AK1:AK" & CStr(.Cells(.Rows.Count,"AK").End(xlUp).Row)).Value2
    AL= .Range("AL1:AL" & CStr(.Cells(.Rows.Count,"AL").End(xlUp).Row)).Value2
    AM= .Range("AM1:AM" & CStr(.Cells(.Rows.Count,"AM").End(xlUp).Row)).Value2
    AN= .Range("AN1:AN" & CStr(.Cells(.Rows.Count,"AN").End(xlUp).Row)).Value2
    AO= .Range("AO1:AO" & CStr(.Cells(.Rows.Count,"AO").End(xlUp).Row)).Value2
    AP= .Range("AP1:AP" & CStr(.Cells(.Rows.Count,"AP").End(xlUp).Row)).Value2
    AQ= .Range("AQ1:AQ" & CStr(.Cells(.Rows.Count,"AQ").End(xlUp).Row)).Value2
    AR= .Range("AR1:AR" & CStr(.Cells(.Rows.Count,"AR").End(xlUp).Row)).Value2
    AAS= .Range("AS1:AS" & CStr(.Cells(.Rows.Count,"AS").End(xlUp).Row)).Value2
    AT= .Range("AT1:AT" & CStr(.Cells(.Rows.Count,"AT").End(xlUp).Row)).Value2
    AU= .Range("AU1:AU" & CStr(.Cells(.Rows.Count,"AU").End(xlUp).Row)).Value2
    AV= .Range("AV1:AV" & CStr(.Cells(.Rows.Count,"AV").End(xlUp).Row)).Value2
    AW= .Range("AW1:AW" & CStr(.Cells(.Rows.Count,"AW").End(xlUp).Row)).Value2
    AX= .Range("AX1:AX" & CStr(.Cells(.Rows.Count,"AX").End(xlUp).Row)).Value2
    AY= .Range("AY1:AY" & CStr(.Cells(.Rows.Count,"AY").End(xlUp).Row)).Value2
    AZ= .Range("AZ1:AZ" & CStr(.Cells(.Rows.Count,"AZ").End(xlUp).Row)).Value2
    BA= .Range("BA1:BA" & CStr(.Cells(.Rows.Count,"BA").End(xlUp).Row)).Value2
    BB= .Range("BB1:BB" & CStr(.Cells(.Rows.Count,"BB").End(xlUp).Row)).Value2
    BC= .Range("BC1:BC" & CStr(.Cells(.Rows.Count,"BC").End(xlUp).Row)).Value2
    BD= .Range("BD1:BD" & CStr(.Cells(.Rows.Count,"BD").End(xlUp).Row)).Value2
    BE= .Range("BE1:BE" & CStr(.Cells(.Rows.Count,"BE").End(xlUp).Row)).Value2
    BF= .Range("BF1:BF" & CStr(.Cells(.Rows.Count,"BF").End(xlUp).Row)).Value2
    BG= .Range("BG1:BG" & CStr(.Cells(.Rows.Count,"BG").End(xlUp).Row)).Value2
    BH= .Range("BH1:BH" & CStr(.Cells(.Rows.Count,"BH").End(xlUp).Row)).Value2
    BI= .Range("BI1:BI" & CStr(.Cells(.Rows.Count,"BI").End(xlUp).Row)).Value2
    BJ= .Range("BJ1:BJ" & CStr(.Cells(.Rows.Count,"BJ").End(xlUp).Row)).Value2
    BK= .Range("BK1:BK" & CStr(.Cells(.Rows.Count,"BK").End(xlUp).Row)).Value2
    BL= .Range("BL1:BL" & CStr(.Cells(.Rows.Count,"BL").End(xlUp).Row)).Value2
    BM= .Range("BM1:BM" & CStr(.Cells(.Rows.Count,"BM").End(xlUp).Row)).Value2
    BN= .Range("BN1:BN" & CStr(.Cells(.Rows.Count,"BN").End(xlUp).Row)).Value2
    BO= .Range("BO1:BO" & CStr(.Cells(.Rows.Count,"BO").End(xlUp).Row)).Value2
    BP= .Range("BP1:BP" & CStr(.Cells(.Rows.Count,"BP").End(xlUp).Row)).Value2
    BQ= .Range("BQ1:BQ" & CStr(.Cells(.Rows.Count,"BQ").End(xlUp).Row)).Value2
    BR= .Range("BR1:BR" & CStr(.Cells(.Rows.Count,"BR").End(xlUp).Row)).Value2
    BS= .Range("BS1:BS" & CStr(.Cells(.Rows.Count,"BS").End(xlUp).Row)).Value2
    BT= .Range("BT1:BT" & CStr(.Cells(.Rows.Count,"BT").End(xlUp).Row)).Value2
    BU= .Range("BU1:BU" & CStr(.Cells(.Rows.Count,"BU").End(xlUp).Row)).Value2
    BV= .Range("BV1:BV" & CStr(.Cells(.Rows.Count,"BV").End(xlUp).Row)).Value2
    BW= .Range("BW1:BW" & CStr(.Cells(.Rows.Count,"BW").End(xlUp).Row)).Value2
    BX= .Range("BX1:BX" & CStr(.Cells(.Rows.Count,"BX").End(xlUp).Row)).Value2
    BBY= .Range("BY1:BY" & CStr(.Cells(.Rows.Count,"BY").End(xlUp).Row)).Value2
    BZ= .Range("BZ1:BZ" & CStr(.Cells(.Rows.Count,"BZ").End(xlUp).Row)).Value2
    CA= .Range("CA1:CA" & CStr(.Cells(.Rows.Count,"CA").End(xlUp).Row)).Value2
    CB= .Range("CB1:CB" & CStr(.Cells(.Rows.Count,"CB").End(xlUp).Row)).Value2
    CC= .Range("CC1:CC" & CStr(.Cells(.Rows.Count,"CC").End(xlUp).Row)).Value2
    CD= .Range("CD1:CD" & CStr(.Cells(.Rows.Count,"CD").End(xlUp).Row)).Value2
    CE= .Range("CE1:CE" & CStr(.Cells(.Rows.Count,"CE").End(xlUp).Row)).Value2
    CF= .Range("CF1:CF" & CStr(.Cells(.Rows.Count,"CF").End(xlUp).Row)).Value2
    CG= .Range("CG1:CG" & CStr(.Cells(.Rows.Count,"CG").End(xlUp).Row)).Value2
    CH= .Range("CH1:CH" & CStr(.Cells(.Rows.Count,"CH").End(xlUp).Row)).Value2
    CI= .Range("CI1:CI" & CStr(.Cells(.Rows.Count,"CI").End(xlUp).Row)).Value2
    CJ= .Range("CJ1:CJ" & CStr(.Cells(.Rows.Count,"CJ").End(xlUp).Row)).Value2
    CK= .Range("CK1:CK" & CStr(.Cells(.Rows.Count,"CK").End(xlUp).Row)).Value2
    CL= .Range("CL1:CL" & CStr(.Cells(.Rows.Count,"CL").End(xlUp).Row)).Value2
    CM= .Range("CM1:CM" & CStr(.Cells(.Rows.Count,"CM").End(xlUp).Row)).Value2
    CN= .Range("CN1:CN" & CStr(.Cells(.Rows.Count,"CN").End(xlUp).Row)).Value2
    CO= .Range("CO1:CO" & CStr(.Cells(.Rows.Count,"CO").End(xlUp).Row)).Value2
    CP= .Range("CP1:CP" & CStr(.Cells(.Rows.Count,"CP").End(xlUp).Row)).Value2
    CQ= .Range("CQ1:CQ" & CStr(.Cells(.Rows.Count,"CQ").End(xlUp).Row)).Value2
    CR= .Range("CR1:CR" & CStr(.Cells(.Rows.Count,"CR").End(xlUp).Row)).Value2
    CS= .Range("CS1:CS" & CStr(.Cells(.Rows.Count,"CS").End(xlUp).Row)).Value2
    CT= .Range("CT1:CT" & CStr(.Cells(.Rows.Count,"CT").End(xlUp).Row)).Value2
    CU= .Range("CU1:CU" & CStr(.Cells(.Rows.Count,"CU").End(xlUp).Row)).Value2
    CV= .Range("CV1:CV" & CStr(.Cells(.Rows.Count,"CV").End(xlUp).Row)).Value2
    CW= .Range("CW1:CW" & CStr(.Cells(.Rows.Count,"CW").End(xlUp).Row)).Value2
    CX= .Range("CX1:CX" & CStr(.Cells(.Rows.Count,"CX").End(xlUp).Row)).Value2
    CY= .Range("CY1:CY" & CStr(.Cells(.Rows.Count,"CY").End(xlUp).Row)).Value2
    CZ= .Range("CZ1:CZ" & CStr(.Cells(.Rows.Count,"CZ").End(xlUp).Row)).Value2
    DA= .Range("DA1:DA" & CStr(.Cells(.Rows.Count,"DA").End(xlUp).Row)).Value2
    DB= .Range("DB1:DB" & CStr(.Cells(.Rows.Count,"DB").End(xlUp).Row)).Value2
    DC= .Range("DC1:DC" & CStr(.Cells(.Rows.Count,"DC").End(xlUp).Row)).Value2
    DD= .Range("DD1:DD" & CStr(.Cells(.Rows.Count,"DD").End(xlUp).Row)).Value2
    DE= .Range("DE1:DE" & CStr(.Cells(.Rows.Count,"DE").End(xlUp).Row)).Value2
    DF= .Range("DF1:DF" & CStr(.Cells(.Rows.Count,"DF").End(xlUp).Row)).Value2
    DG= .Range("DG1:DG" & CStr(.Cells(.Rows.Count,"DG").End(xlUp).Row)).Value2
    DH= .Range("DH1:DH" & CStr(.Cells(.Rows.Count,"DH").End(xlUp).Row)).Value2
    DI= .Range("DI1:DI" & CStr(.Cells(.Rows.Count,"DI").End(xlUp).Row)).Value2
    DJ= .Range("DJ1:DJ" & CStr(.Cells(.Rows.Count,"DJ").End(xlUp).Row)).Value2
    DK= .Range("DK1:DK" & CStr(.Cells(.Rows.Count,"DK").End(xlUp).Row)).Value2
    DL= .Range("DL1:DL" & CStr(.Cells(.Rows.Count,"DL").End(xlUp).Row)).Value2
    DM= .Range("DM1:DM" & CStr(.Cells(.Rows.Count,"DM").End(xlUp).Row)).Value2
    DN= .Range("DN1:DN" & CStr(.Cells(.Rows.Count,"DN").End(xlUp).Row)).Value2
    DDO= .Range("DO1:DO" & CStr(.Cells(.Rows.Count,"DO").End(xlUp).Row)).Value2
    DP= .Range("DP1:DP" & CStr(.Cells(.Rows.Count,"DP").End(xlUp).Row)).Value2
    DQ= .Range("DQ1:DQ" & CStr(.Cells(.Rows.Count,"DQ").End(xlUp).Row)).Value2
    DR= .Range("DR1:DR" & CStr(.Cells(.Rows.Count,"DR").End(xlUp).Row)).Value2
    DS= .Range("DS1:DS" & CStr(.Cells(.Rows.Count,"DS").End(xlUp).Row)).Value2
    DT= .Range("DT1:DT" & CStr(.Cells(.Rows.Count,"DT").End(xlUp).Row)).Value2
    DU= .Range("DU1:DU" & CStr(.Cells(.Rows.Count,"DU").End(xlUp).Row)).Value2
    DV= .Range("DV1:DV" & CStr(.Cells(.Rows.Count,"DV").End(xlUp).Row)).Value2
    DW= .Range("DW1:DW" & CStr(.Cells(.Rows.Count,"DW").End(xlUp).Row)).Value2
    DX= .Range("DX1:DX" & CStr(.Cells(.Rows.Count,"DX").End(xlUp).Row)).Value2
    DY= .Range("DY1:DY" & CStr(.Cells(.Rows.Count,"DY").End(xlUp).Row)).Value2
    DZ= .Range("DZ1:DZ" & CStr(.Cells(.Rows.Count,"DZ").End(xlUp).Row)).Value2


    End With
    Open f & "1.xls" For Output As fi
    For no = 1 To UBound(A)
    If (no - 1) Mod cnst = 0 Then
    Close fi
    Open f & CStr(no) & ".xls" For Output As fi
    End If
    Print #fi, A(no, 1) & vbTab & B(no, 1) & vbTab & C(no, 1) & vbTab & D(no, 1) & vbTab & E(no, 1) & vbTab & FF(no, 1) & vbTab & G(no, 1) & vbTab &
    H(no, 1) & vbTab & I(no, 1) & vbTab & J(no, 1) & vbTab & K(no, 1) & vbTab & L(no, 1) & vbTab & M(no, 1) & vbTab & N(no, 1) & vbTab &
    O(no, 1) & vbTab & P(no, 1) & vbTab & Q(no, 1) & vbTab & R(no, 1) & vbTab & S(no, 1) & vbTab & T(no, 1) & vbTab & U(no, 1) & vbTab &
    V(no, 1) & vbTab & W(no, 1) & vbTab & X(no, 1) & vbTab & Y(no, 1) & vbTab & Z(no, 1) & vbTab &
    AA(no, 1) & vbTab & AB(no, 1) & vbTab & AC(no, 1) & vbTab & AD(no, 1) & vbTab & AE(no, 1) & vbTab & AF(no, 1) & vbTab &
    AG(no, 1) & vbTab & AH(no, 1) & vbTab & AI(no, 1) & vbTab & AJ(no, 1) & vbTab & AK(no, 1) & vbTab & AL(no, 1) & vbTab &
    AM(no, 1) & vbTab & AN(no, 1) & vbTab & AO(no, 1) & vbTab & AP(no, 1) & vbTab & AQ(no, 1) & vbTab & AR(no, 1) & vbTab &
    AAS(no, 1) & vbTab & AT(no, 1) & vbTab & AU(no, 1) & vbTab & AV(no, 1) & vbTab & AW(no, 1) & vbTab & AX(no, 1) & vbTab &
    AY(no, 1) & vbTab & AZ(no, 1) & vbTab &
    BA(no, 1) & vbTab & BB(no, 1) & vbTab & BC(no, 1) & vbTab & BD(no, 1) & vbTab & BE(no, 1) & vbTab & BF(no, 1) & vbTab &
    BG(no, 1) & vbTab & BH(no, 1) & vbTab & BI(no, 1) & vbTab & BJ(no, 1) & vbTab & BK(no, 1) & vbTab & BL(no, 1) & vbTab &
    BM(no, 1) & vbTab & BN(no, 1) & vbTab & BO(no, 1) & vbTab & BP(no, 1) & vbTab & BQ(no, 1) & vbTab & BR(no, 1) & vbTab &
    BS(no, 1) & vbTab & BT(no, 1) & vbTab & BU(no, 1) & vbTab & BV(no, 1) & vbTab & BW(no, 1) & vbTab & BX(no, 1) & vbTab &
    BBY(no, 1) & vbTab & BZ(no, 1) & vbTab &
    CA(no, 1) & vbTab & CB(no, 1) & vbTab & CC(no, 1) & vbTab & CD(no, 1) & vbTab & CE(no, 1) & vbTab & CF(no, 1) & vbTab &
    CG(no, 1) & vbTab & CH(no, 1) & vbTab & CI(no, 1) & vbTab & CJ(no, 1) & vbTab & CK(no, 1) & vbTab & CL(no, 1) & vbTab &
    CM(no, 1) & vbTab & CN(no, 1) & vbTab & CO(no, 1) & vbTab & CP(no, 1) & vbTab & CQ(no, 1) & vbTab & CR(no, 1) & vbTab &
    CS(no, 1) & vbTab & CT(no, 1) & vbTab & CU(no, 1) & vbTab & CV(no, 1) & vbTab & CW(no, 1) & vbTab & CX(no, 1) & vbTab &
    CY(no, 1) & vbTab & CZ(no, 1) & vbTab &
    DA(no, 1) & vbTab & DB(no, 1) & vbTab & DC(no, 1) & vbTab & DD(no, 1) & vbTab & DE(no, 1) & vbTab & DF(no, 1) & vbTab &
    DG(no, 1) & vbTab & DH(no, 1) & vbTab & DI(no, 1) & vbTab & DJ(no, 1) & vbTab & DK(no, 1) & vbTab & DL(no, 1) & vbTab &
    DM(no, 1) & vbTab & DN(no, 1) & vbTab & DOO(no, 1) & vbTab & DP(no, 1) & vbTab & DQ(no, 1) & vbTab & DR(no, 1) & vbTab &
    DS(no, 1) & vbTab & DT(no, 1) & vbTab & DU(no, 1) & vbTab & DV(no, 1) & vbTab & DW(no, 1) & vbTab & DX(no, 1) & vbTab &
    DY(no, 1) & vbTab & DZ(no, 1)




    Next
    Close fi
    End Sub

  • Hello,


    It is always preferable to start your own thread ...


    In addition, its a 3 month old thread ... !!!

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!