0

Was hoping to get some advice on my partially working code, I had received good advice on VBA code to copy rows based on cell value (H) and paste into a new sheet named after the cell value.

However, the code I am using gives a run-time error and after Excel stops responding/restarts it seems like the code only ran through half the data. Any and all advice is appreciated.

Sub CopyCodeshort()

    Application.ScreenUpdating = False
    Dim rCell As Range
    Dim lastrow As Long
    Dim shtData As Worksheet, shtDest As Worksheet
    Dim sheetName As String

    Set shtData = Worksheets("Data")

    lastrow = shtData.Cells(Rows.Count, 1).End(xlUp).Row
    For Each rCell In shtData.Range("H2:H" & lastrow).SpecialCells(xlCellTypeConstants)

        sheetName = rCell.Value
        If Not SheetExists(sheetName) Then
            Set shtDest = Worksheets.Add(, Worksheets(Worksheets.Count))
            shtDest.Name = sheetName
            shtData.Rows(1).EntireRow.Copy shtDest.Rows(1)
        Else
            Set shtDest = Worksheets(sheetName)
        End If

        shtDest.Range("H" & Rows.Count).End(xlUp).Offset(1, 0).EntireRow.Value = _
                                                            rCell.EntireRow.Value

    Next rCell
    Application.ScreenUpdating = True

End Sub

EDIT: After taking your awesome advice, I made some slight tweaks and now Excel doesn't crash anymore, but I still get an error stating I don't have enough memory and then I get run-time error 1004. Partial results are getting more comprehensive, but still missing data.

Mesut Akcan
  • 899
  • 7
  • 19
user3587469
  • 3
  • 3
  • 1
    Which line is the error on? – Tim Williams Dec 10 '18 at 23:40
  • My first guess would be to check for error values in your data. – Display name Dec 10 '18 at 23:41
  • No error values in the data @O.PAL – user3587469 Dec 11 '18 at 00:03
  • @TimWilliams it's strange half the time I run thru debugger there are no errors and other times I get "sub or function not defined" – user3587469 Dec 11 '18 at 00:08
  • 1
    When you get an error, what line is it on? *RTE* implies the error occurs mid run – urdearboy Dec 11 '18 at 00:09
  • Whats your `SheetExists()` code ? – Display name Dec 11 '18 at 00:12
  • I tried your code with made up data and SheetExists() UDF and its working just fine so either the data or you SheetsExists() UDF is faulty. [This](https://stackoverflow.com/questions/35861459/excel-vba-run-time-error-method-value-of-object-range-failed-but-only-on) could also be worth looking at maybe. – Display name Dec 11 '18 at 00:22
  • Also, do you really need the `EntireRow.Value` to be equal to `rCell`? – urdearboy Dec 11 '18 at 00:45
  • @O.PAL Ya the code definitely works, I think it has more to do with the amount of data, going to look into the link you posted and update back tmrw. Thanks – user3587469 Dec 11 '18 at 00:51
  • @urdearboy I have no idea what line exacly because Excel always crashes when I run this code. If i don't get the run time error, I'll get another error that says not enough memory. After excel restarts, I get partial results. – user3587469 Dec 11 '18 at 00:52
  • Any other code in your book? Any `Events`, like maybe `Change_Event` or `Worksheet` related events? – urdearboy Dec 11 '18 at 00:53
  • @urdearboy Just doubled checked , there are no other codes in this workbook – user3587469 Dec 11 '18 at 01:11
  • Well number of worksheet in a workbook is [limited by memory](https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3) so depending on how many worksheet you create with this code this might be the problem. – Display name Dec 11 '18 at 01:26
  • @O.PAL I see... currently I'd need 15 new worksheets created. Is there any way to circumvent the memory issue? – user3587469 Dec 12 '18 at 16:33
  • 15 Sheets does not sound like a big deal, it depends on the data you manipulate. I would suggest making a few different test to validate the memory issue, like progressively increasing the number of created sheets with various data size and check if and when the error/crash is still happening. You could also try to run it on a different machine and or with a new file/excel version... – Display name Dec 12 '18 at 22:36

0 Answers0