jbesclapez
Active Member
- Joined
- Feb 6, 2010
- Messages
- 275
Hi,
I have a macro that turns a cross table into a flat table.
The 2 first columns are repeated and should be on the flat table...
The 3rd column and after are where the values are. So i copy the range of the columsn after column 4.
It goes like this :
It works on many columns but after row 36 it says ERROR 6 OVERFLOW
Thanks for guiding me
I have a macro that turns a cross table into a flat table.
The 2 first columns are repeated and should be on the flat table...
The 3rd column and after are where the values are. So i copy the range of the columsn after column 4.
It goes like this :
It works on many columns but after row 36 it says ERROR 6 OVERFLOW
Thanks for guiding me
VBA Code:
Sub CrosstableToFlat()
Application.ScreenUpdating = False
Dim DateOS As String
Dim LastCol1 As Integer
Dim LastRowA As Integer
Dim i As Integer
Dim j As Integer
Dim sht As Worksheet
Dim NewRowA As Integer
Set sht = ActiveSheet
DateOS = Range("A2").Value
LastRowA = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
LastCol1 = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
For i = 4 To LastCol1
NewRowA = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row + 1
Range("D2:D" & LastRowA).Copy 'Copy Column D
Range("C" & NewRowA).PasteSpecial Paste:=xlPasteValues 'Paste Col D in C
Range("A2:B" & LastRowA).Copy
Range("A" & NewRowA).PasteSpecial Paste:=xlPasteValues
Columns(4).EntireColumn.Delete
Next i
' Range("A2:A" & LastRowA).Copy
' Range("B" & LastRowA + 1).PasteSpecial Paste:=xlPasteValues
Application.ScreenUpdating = True
End Sub