VBA Error '4001'

Question

I have 4002 rows and 5257 columns i.e. A1 to GTE4002. I have written a macro to swap rows and columns, create a new sheet and paste. 

Dim ws1 As Worksheet

Sheets("MainSheet").Activate

Sheets("MainSheet").Cells.Select

'Range("A1:GTE4002").Select

Selection.Copy

Set ws1 = Sheets.Add(After:=ActiveSheet)

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=True

Sheets(ws1).Select

Sheets(ws1).Name = "SHEET1"

I got a runtime error as shown in the fig. Is there anyway to debug this error?

Answer 1

You commented out the line to select A1:GTE4002, so your code operates on Cells, which consists of 1048576 rows and 16384 columns. This range cannot be transposed since there is no room for 1048576 columns.

Here is a version that copies only A1:GTE4002:

Sub CopyMainSheet()

    Dim ws1 As Worksheet

    Set ws1 = Sheets.Add(After:=ActiveSheet)

    ws1.Name = "SHEET1"

    Sheets("MainSheet").Range("A1:GTE4002").Copy

    ws1.Range("A1").PasteSpecial Transpose:=True

End Sub

Or if you want to copy/paste the used range of MainSheet:

Sub CopyMainSheet()

    Dim ws1 As Worksheet

    Set ws1 = Sheets.Add(After:=ActiveSheet)

    ws1.Name = "SHEET1"

    Sheets("MainSheet").UsedRange.Copy

    ws1.Range("A1").PasteSpecial Transpose:=True

End Sub

Answer 2

Thanks. It worked :)