Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 26

Thread: For Every 1500 Rows Copy To New Sheet & Save

  1. #11
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: For Every 1500 Rows Copy To New Sheet & Save

    Quote Originally Posted by JoeMo View Post
    You could also consider limiting the array to 1500 rows per loop, something like this:
    Code:
    
        ary = ws.Range(ws.Cells(Ct * NumRws + 1, "A"), ws.Cells(NumRws * (Ct + 1), lastCol)).Value2
    AH!
    its perfect, so fast.
    any idea how i would go about doing the inverse of this?
    One that says for each sheet add the current region as part of the array?


    Code:
    Sub arrangeADD()
    
       Dim i As Long, j As Long
       Dim was As Worksheet
       Dim Ary As Variant
       Dim lastRow As Long
       Dim destRow As Long
    
    
        ThisWorkbook.Sheets.Add After:=ThisWorkbook.Sheets(1)
        Set ws = ActiveSheet
        
       For i = Worksheets("Program Start").Index + 1 To Worksheets("Description Helper").Index - 1
                Ary = Sheets(i).Range("A1").CurrentRegion.Value2
       Next i
                ws.Range("A1").Resize(UBound(Ary), 19).Value = Ary
    End Sub
    so i've added a sheet and i want to condense multiple sheets into one array
    so instead of limiting the array to 1500 from a sheet we add multiple sheets of a workbook to an array
    so lets say we run your code and then we want to inverse it by adding the sheets between "Program Start" and "Description Helper" to an array
    but my code here only copies the last sheet in the loop because i don't know how to add to an array i guess? if that makes sense
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  2. #12
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: For Every 1500 Rows Copy To New Sheet & Save

    Quote Originally Posted by MARK858 View Post
    Why? you are writing a continuous range to the sheets the same number of times.
    i don't know the technicalities but i can assure you that JoeMo's code is faster than Dante's
    Its the same for all of my code now. I've overhauled my entire system of macros to run with arrays and dictionaries and its cutting multiple hours of processing code per day.
    i believe its the difference in writing vs copy pasting. one is sequential the other is not?
    don't know for sure but that is my guestimate
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  3. #13
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,109
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: For Every 1500 Rows Copy To New Sheet & Save

    You'll probably find that it is the use of copy in DanteAmor's code which is using the clipboard that is making the big difference in this case, if it was a .Value = .Value then I would suspect the speed would be closer.

    The advantage of using an array is when it is cutting down the number of interactions with a sheet for instance if you looped through 10 rows, copying and pasting alternate rows each time then you would interact with the sheet 5 times, if you fed the values into an array then you would interact with the sheet only once.

    I have no problem using arrays for this, I was just wondering why it was specifically using the array that would make the code faster.
    Last edited by MARK858; Aug 28th, 2019 at 02:55 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  4. #14
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,492
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: For Every 1500 Rows Copy To New Sheet & Save

    Quote Originally Posted by BlakeSkate View Post
    AH!
    its perfect, so fast.
    any idea how i would go about doing the inverse of this?
    One that says for each sheet add the current region as part of the array?


    Code:
    Sub arrangeADD()
    
       Dim i As Long, j As Long
       Dim was As Worksheet
       Dim Ary As Variant
       Dim lastRow As Long
       Dim destRow As Long
    
    
        ThisWorkbook.Sheets.Add After:=ThisWorkbook.Sheets(1)
        Set ws = ActiveSheet
        
       For i = Worksheets("Program Start").Index + 1 To Worksheets("Description Helper").Index - 1
                Ary = Sheets(i).Range("A1").CurrentRegion.Value2
       Next i
                ws.Range("A1").Resize(UBound(Ary), 19).Value = Ary
    End Sub
    so i've added a sheet and i want to condense multiple sheets into one array
    so instead of limiting the array to 1500 from a sheet we add multiple sheets of a workbook to an array
    so lets say we run your code and then we want to inverse it by adding the sheets between "Program Start" and "Description Helper" to an array
    but my code here only copies the last sheet in the loop because i don't know how to add to an array i guess?
    if that makes sense
    I haven't got the time right now to help with this, but I would consider creating an array of arrays, let's call it AryOfSheets, each element of which holds the data from one sheet and uses the sheet index to identify it. So, first sheet data goes into ary1, next sheet to ary2 and so on. Then you process AryOfSheets to do whatever you want with any or all of the sheets it holds.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #15
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: For Every 1500 Rows Copy To New Sheet & Save

    Quote Originally Posted by JoeMo View Post
    So, first sheet data goes into ary1, next sheet to ary2 and so on.
    defining and calling these sheet arrays seems to be difficult considering they need to by dynamic
    Code:
       Dim i As Long, j As Long
       Dim ws As Worksheet
       Dim aryEATER As Variant
       numSHEETS = (Worksheets("Description Helper").Index - 2)
       ReDim Ary(numSHEETS) As Variant
    
        ThisWorkbook.Sheets.Add After:=ThisWorkbook.Sheets(1)
        Set ws = ActiveSheet
        
       For i = 1 To numSHEETS
        Sheets(i).Range("A1").CurrentRegion.Value2 = Ary & i
       Next i
    my head is spinning
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  6. #16
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,492
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: For Every 1500 Rows Copy To New Sheet & Save

    Quote Originally Posted by BlakeSkate View Post
    defining and calling these sheet arrays seems to be difficult considering they need to by dynamic
    Code:
       Dim i As Long, j As Long
       Dim ws As Worksheet
       Dim aryEATER As Variant
       numSHEETS = (Worksheets("Description Helper").Index - 2)
       ReDim Ary(numSHEETS) As Variant
    
        ThisWorkbook.Sheets.Add After:=ThisWorkbook.Sheets(1)
        Set ws = ActiveSheet
        
       For i = 1 To numSHEETS
        Sheets(i).Range("A1").CurrentRegion.Value2 = Ary & i
       Next i
    my head is spinning
    Maybe:
    Code:
    Dim MyArrays As Variant
    Dim i As Long
    Dim numSHEETS As Long
    numSHEETS = (Worksheets("Description Helper").Index - 2)
    ReDim MyArrays(1 To numSHEETS)
    For i = 1 To UBound(MyArrays)
        MyArrays(i) = Sheets(i).Range("A1").CurrentRegion.Value2  'ary & i is the ith element in MyArrays
    Next i
    Last edited by JoeMo; Aug 28th, 2019 at 04:28 PM.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  7. #17
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: For Every 1500 Rows Copy To New Sheet & Save

    Quote Originally Posted by JoeMo View Post
    MyArrays(i) = Sheets(i).Range("A1").CurrentRegion.Value2 'ary & i is the ith element in MyArrays
    i actually tried that method of writing to the array but i didn't have "1 to numsheets" so it was just reading it as MyArrays(4)
    i see the error of my ways

    now to just understand how to write the array of arrays to a sheet

    Code:
    Sub blakeskate()
    
    
    Dim MyArrays As Variant
    Dim i As Long, x As Long
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim numSHEETS As Long
    numSHEETS = (Worksheets("Description Helper").Index - 1) ' doesn't unclude description helper
    ReDim MyArrays(2 To numSHEETS) ' skips program start sheet
    
    
    
    For i = LBound(MyArrays) To UBound(MyArrays)
        MyArrays(i) = Sheets(i).Range("A1").CurrentRegion.Value2
    Next i
    
    Worksheets.Add After:=ActiveSheet
    Set ws = ActiveSheet
    
    For x = LBound(MyArrays) To UBound(MyArrays)
            ws.Range("A1").Resize(UBound(MyArrays(x)), 19).Value = MyArray
    Next x
        
    End Sub
    can i even use resize since the size of this array is technically 4 when running this on 4 sheets?
    or does it need to be something like MyArrays(x)(y) for the ubound of all 4 sheets?
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  8. #18
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,492
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: For Every 1500 Rows Copy To New Sheet & Save

    Quote Originally Posted by BlakeSkate View Post
    i actually tried that method of writing to the array but i didn't have "1 to numsheets" so it was just reading it as MyArrays(4)
    i see the error of my ways

    now to just understand how to write the array of arrays to a sheet

    can i even use resize since the size of this array is technically 4 when running this on 4 sheets?
    or does it need to be something like MyArrays(x)(y) for the ubound of all 4 sheets?
    Lightly tested, but see if this comes close to what you want.
    Code:
    Sub CondenseSheetsData()
    Dim MyArrays As Variant
    Dim i As Long
    Dim numSHEETS As Long
    Dim NxRw As Long
    Dim V As Variant
    numSHEETS = 5    '(Worksheets("Description Helper").Index - 1)
    ReDim MyArrays(1 To numSHEETS)
    For i = 1 To UBound(MyArrays)
        MyArrays(i) = Sheets(i).Range("A1").CurrentRegion.Value2  'MyArrays(i) is the equivalent of ary & i, i.e. a 2-D array of sheet(i) values
    Next i
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("CondensedSheets").Delete
    On Error GoTo 0
    Worksheets.Add after:=Sheets(numSHEETS)
    ActiveSheet.Name = "CondensedSheets"
    NxRw = 1
    For i = 1 To UBound(MyArrays)
        V = MyArrays(i)
        Range(Cells(NxRw, 1), Cells(NxRw + UBound(V, 1) - 1, NxRw + UBound(V, 2) - 1)) = V
        NxRw = Cells(Rows.Count, 1).End(xlUp).Row + 1
        Erase V
    Next i
    Sheets("CondensedSheets").Cells.Replace "#N/A", ""
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    End Sub
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  9. #19
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: For Every 1500 Rows Copy To New Sheet & Save

    Quote Originally Posted by JoeMo View Post
    Lightly tested, but see if this comes close to what you want.
    this actually crashes my work pc
    i was thinking this would be less stress than my current method of nested vlookups being copied down to the sum of all the lastrows of sheets and then pasting as values
    apparently.....not
    i may have to give up on the array of arrays idea and yeild to dynamics, and just create an array for each sheet.
    but again i have no clue as to what the latter half of your code says/does


    would it be possible to paste 1 array to a destination row?
    like lets says i use an array to grab Sheet2 and write it to CondensedSheets
    then i take Sheet3 and write it to CondensedSheets at the destination row ?
    like

    Code:
    Sub BlakeSkate()
       Dim i As Long, j As Long
       Dim lastRow as Long
       Dim destRow as Long
       Dim Ary As Variant
       Dim sStart as Long
       Dim sEnd as Long
       
      'Loops through the sheets 
       For i = Worksheets("Program Start").Index + 1 To Worksheets("Master Image").Index - 1
          With Sheets(i)
             Ary = .Range("A1").CurrentRegion.Value2
    
          'writes the array back to a sheet using destination row
          destrow = Sheets("CondensedSheets").Range("A" & Rows.Count).End(xlUp).Row + 1
          Sheets("CondensedSheets").Range("A" & destRow).Resize(UBound + destRow - 1(Ary), 19).Value = Ary
          End With
       Next i
    End Sub
    basically if i can write an array to the row after the last row of the sheet i should be good.
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  10. #20
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,492
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: For Every 1500 Rows Copy To New Sheet & Save

    Quote Originally Posted by BlakeSkate View Post
    this actually crashes my work pc
    i was thinking this would be less stress than my current method of nested vlookups being copied down to the sum of all the lastrows of sheets and then pasting as values
    apparently.....not
    i may have to give up on the array of arrays idea and yeild to dynamics, and just create an array for each sheet.
    but again i have no clue as to what the latter half of your code says/does


    would it be possible to paste 1 array to a destination row?
    like lets says i use an array to grab Sheet2 and write it to CondensedSheets
    then i take Sheet3 and write it to CondensedSheets at the destination row ?
    like

    Code:
    Sub BlakeSkate()
       Dim i As Long, j As Long
       Dim lastRow as Long
       Dim destRow as Long
       Dim Ary As Variant
       Dim sStart as Long
       Dim sEnd as Long
       
      'Loops through the sheets 
       For i = Worksheets("Program Start").Index + 1 To Worksheets("Master Image").Index - 1
          With Sheets(i)
             Ary = .Range("A1").CurrentRegion.Value2
    
          'writes the array back to a sheet using destination row
          destrow = Sheets("CondensedSheets").Range("A" & Rows.Count).End(xlUp).Row + 1
          Sheets("CondensedSheets").Range("A" & destRow).Resize(UBound + destRow - 1(Ary), 19).Value = Ary
          End With
       Next i
    End Sub
    basically if i can write an array to the row after the last row of the sheet i should be good.
    I tested the code I posted on a workbook with 5 sheets and it ran fine for me albeit with far fewer populated rows on each sheet than you have on your sheets. I don't see any difference between the array of arrays and writing one array at a time to the condensed sheet, but you can certainly do it one-at-a-time by looping through the sheets.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •