0

I have a macro which "chops up" spread sheets, allowing the user to select columns they want to keep. As well as selection criteria to filter columns for values and date ranges.

The macro works fine, however when I tried to process a 190MB file I got an

error: 1004 copy method of range class failed

The line it failed on was:

Selection.SpecialCells(xlCellTypeVisible).Copy Destination:=Export.Sheets("Sheet1").Range("A1")

I've tried splitting the code up and it still didn't work. (see below)

Selection.SpecialCells(xlCellTypeVisible).Copy Export.Sheets("Sheet1").Range("A1").Select Export.Sheets("Sheet1").Range("A1").Paste

The full file can be found here: https://quickfileshare.org/9th/Big_Choppa_-_V4_JB1_Test.xlsm

braX
  • 11,506
  • 5
  • 20
  • 33
Mobenator
  • 11
  • 2
  • 4
    This seems like a bigger problem than a copy failing. My jaw dropped when I saw 190MB - that is way too much for Excel. – BigBen May 25 '18 at 15:53
  • 3
    You have a 190MB file and are using `.Select` in a macro? That's a big red flag. Please see [how to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), that can greatly improve the code/speed/etc. depending on what's going on. It looks like you're doing `.Copy ....Select` in the same line? AFAIK you can't do that. Also, please note that your file is not likely to be downloaded (personally, I am not downloading any random macro-enabled files). Can you instead expand your question with a [mcve]? – BruceWayne May 25 '18 at 16:26
  • @BigBen - D'oh, I focused on his second one...also can't quite tell if his first code is all on one line or if it's actually split on two when OP runs it. But yeah, nevermind! – BruceWayne May 25 '18 at 16:31
  • If you really have to use Excel for the data of that size you should look into utilizing `ADODB` and SQL queires. When I had to work with a 300 MB file in Excel, querying was the least painful way of working with it. – Victor K May 25 '18 at 16:34

0 Answers0