excel forces data to merge in some cells for a large file (84,328 rows by 78 columns) excel 2013

[George]

Hi all,

unusual problem here:
I'm importing data from one excel spreadsheet to another using the following code:

        Cells.Select
        Selection.Copy
       
        SourceWBName = ActiveWorkbook.Name
       
        Application.DisplayAlerts = False                                                          'turning off alert boxes like "there's a lot of data in the clipboard" etc.
       
        If ActiveWorkbook.Name <> ThisWorkbook.Name Then                                            'Double sure we don't close this workbook
           
            ActiveWorkbook.Close False
       
        End If
       
        ThisWorkbook.Activate
   
        ActiveSheet.Paste

and for some reason it merges some data in a few columns so that rather than 84,328 columns I have 83,696.

I'm guessing that it's probably something to do with the fact that I'm closing the workbook before pasting, but I'm not totally sure how to deal with that - any suggestions or other things that could be causing it?

There's no way it's a shortage of RAM or anything else like that (fairly high spec computer), and in case it matters I'm running excel 2013.

Cheers,

George

 

[George]

In case anyone is thinking about this, don't worry, I'm an idiot (solved the problem). In case anyone else runs across an issue along the lines of this (or wants to steal the code for any reason) this is how it should look:

      Cells.Select
        Selection.Copy
       
        SourceWBName = ActiveWorkbook.Name
       
        ThisWorkbook.Activate
   
        ActiveSheet.Paste
       
        Workbooks(SourceWBName).Activate
       
        Application.DisplayAlerts = False                                                          'turning off alert boxes like "there's a lot of data in the clipboard" etc.
       
        If ActiveWorkbook.Name <> ThisWorkbook.Name Then                                            'Double sure we don't close this workbook
           
            ActiveWorkbook.Close False
       
        End If

 

[NARAYANK991]

Hi George ,

I am confused ; your topic title says 84328 rows , while within the post , you mention 84328 columns.

Anyway , have you tried using : Activesheet.Paste Range("A1") ?

In case the destination is not specified , the paste occurs at the cursor location.

Narayan

 

[George]

Sorry Narayan, the topic is right, and the body is wrong (although see above, managed to sort the problem).

If you know why it did that or how it chose which cells to merge things in to I'd be very grateful to know.

[the range A1 is selected earlier in the code, but I didn't put that bit in because there's a load of other stuff there too which wasn't related to the issue]

 

[Marc L]

Hi,​

​

why copy more than 1 million lines by more than 16K columns instead of only used cells ?!​

​

See property Worksheet.UsedRange …​

​

And no need Select, Selection and Activate to Copy ! See Copy's help …​

​