0

I am writting a macro that is supposed to copy entire rows from a certain sheet ("Global_IB") to another sheet ("Interpolated Value") in the same Excel file. I need to copy and paste the information in order to exploit the data without having to touch anything in the original data base.

In the code below, I basically go ahead and find the last row of my data base: LR. I then proceed to copy the first three columns of "Global_IB" (without the title row), and paste them in "Interpolated Value) in rows A, B, and C. And so on for the other rows that I am interested in.

This code works perfectly well as long as "Global_IB" has a reasonable amount of rows.

When I try to use the entire file (52 000 rows), an error appears: "Copy Method of Class range failed".

I have read that it may have to do with the fact that excel is limiting the amount of rows it can copy at once to 2516, but funily enough it works for me when I use 3000 rows etc.

I would be interested to know if you have an idea what the problem is and how I could solve it. Maybe a loop that copies and pastes a limited number of rows at a time. If yes, how would you adapt the code ? I can't seem to do it right on my own.

Thank you very much for your help!

Private Sub copieIB()
        
Dim LR As Long
        
LR = Sheets("Global_IB").Cells(Rows.Count, 3).End(xlUp).Row
        
Application.Calculation = xlManual
        
'Copy in Interpolated Value
    
Sheets("Interpolated_Value").Range("A2").Resize(LR - 1, 3).Value = Sheets("Global_IB").Cells(2, 4).Resize(LR - 1, 3).Value
Sheets("Interpolated_Value").Range("D2").Resize(LR - 1, 1).Value = Sheets("Global_IB").Cells(2, 8).Resize(LR - 1, 1).Value
Sheets("Interpolated_Value").Range("E2").Resize(LR - 1, 1).Value = Sheets("Global_IB").Cells(2, 10).Resize(LR - 1, 1).Value
Sheets("Interpolated_Value").Range("F2").Resize(LR - 1, 1).Value = Sheets("Global_IB").Cells(2, 14).Resize(LR - 1, 1).Value
Sheets("Interpolated_Value").Range("G2").Resize(LR - 1, 1).Value = Sheets("Global_IB").Cells(2, 18).Resize(LR - 1, 1).Value
            
Application.Calculation = xlAutomatic
            
End Sub
Janson Martin
  • 13
  • 4
  • Something else is going on ... you can copy an entire sheet w/o problem manually. – BigBen Jun 10 '21 at 13:15
  • Did you try without `resize` before? – Kin Siang Jun 10 '21 at 13:15
  • @BigBen That's what I figured as well, but I can't seem to find the reason of my problem. I am fairly new to VBA. – Janson Martin Jun 10 '21 at 13:28
  • What line throws the error? What is the value of `LR` when it fails? Do these cells contain formulas, or just values? Side note, if you just need the value you can bypass the clipboard and transfer the `.Value` directly. – BigBen Jun 10 '21 at 13:29
  • @KinSiang What do you mean by that ? If I don't resize I won't be able to select the rows without the title row, and copy only until the last row; or I am wrong ? – Janson Martin Jun 10 '21 at 13:29
  • @BigBen the error is for `Sheets("Global_IB").Cells(2, 4).Resize(LR - 1, 3).Copy Sheets("Interpolated_Value").Range("$A$2")`. So I just add .Value at the end of my copy instruction ? – Janson Martin Jun 10 '21 at 13:32
  • No you actually don't `Copy` at all but use value transfer: see [this answer](https://stackoverflow.com/a/51528368/9245853) for example. You assign the `.Value` of your source range to the `.Value` of the (resized) destination range. – BigBen Jun 10 '21 at 13:33
  • @BigBen Thank you, but I am not quite sure how to adapt this method to my particular case. I don't understand eveything that is done there. I have been trying for the past 20minutes. Any ideas ? – Janson Martin Jun 10 '21 at 13:56
  • `Sheets("Interpolated_Value").Range("A2").Resize(LR - 1, 3).Value = Sheets("Global_IB").Cells(2, 4).Resize(LR - 1, 3).Value`. – BigBen Jun 10 '21 at 13:57
  • 1
    @BigBen thank you! It seems to be working, thank you so much! – Janson Martin Jun 10 '21 at 14:05
  • @BigBen actually it did not. A new error appears: "Application-defined or object-defined error". It is a little confusing as it was working the first time. But now the error message appears exactly on the line you wrote above. – Janson Martin Jun 10 '21 at 15:59
  • `Debug.Print LR`, it's probably not what you expect. – BigBen Jun 10 '21 at 16:00
  • @BigBen it actually is the value I expect: the number of rows in my data base – Janson Martin Jun 10 '21 at 16:44
  • Can you [edit] your question with the new code that's throwing the error. – BigBen Jun 10 '21 at 16:52
  • @BigBen the macro is activated using a button, if it is of any help – Janson Martin Jun 10 '21 at 16:52
  • @BigBen code is updated – Janson Martin Jun 10 '21 at 16:55
  • Do you have any lines of data that begin with `=` or `-` and aren't a formula? – BigBen Jun 10 '21 at 16:56
  • @BigBen no, and no empty cells either – Janson Martin Jun 10 '21 at 16:59
  • Try hard-coding a value for `LR`, i.e. `LR = 1000` and see if that succeeds. Test different values to see where it begins to fail. – BigBen Jun 10 '21 at 17:00
  • @BigBen worked well for 1000, 2000, 5000, but as I tried for 10 000 I was shown an error message saying I had selected too much data at once. So I assume the issue here is my computer right ? (It also suggest using the 64bit version of excel, what do you think?) – Janson Martin Jun 10 '21 at 17:09
  • Something else is the issue... I can't repro this even with 50000 rows of data. What is the specific error message you get saying you had selected too much data? Do you have any add-ins installed? – BigBen Jun 10 '21 at 17:14
  • @BigBen it's in French, but in english it would be something around the lines of "Insufficient memory to finish this action. Select less data or close some applications. To improve memory availability, you can try using excel 64 bits." – Janson Martin Jun 10 '21 at 17:47
  • @BigBen no add-ins installed. It's so weird, when I run it just for the first line, it seems to work (despite the "Application-defined or object-defined error"), but as soon as I want to check the result, eveything freezes and the message above about memory appears. And eventually excel crashes. – Janson Martin Jun 10 '21 at 17:49
  • Do you have a `Worksheet_Change` event in the Interpreted Value sheet? – BigBen Jun 10 '21 at 18:00
  • @BigBen not sure what is meant by this, but no I have no such function – Janson Martin Jun 10 '21 at 18:14
  • 1
    @BigBen it gets even weirder. I tried it on a totally new excel file, with no formulas etc, and it works in less than a second. – Janson Martin Jun 10 '21 at 18:53

0 Answers0