domiereavron
New Member
- Joined
- Apr 11, 2013
- Messages
- 22
Every time I run this macro it causes my Excel to crash. I am trying to take data from one sheet and re-arrange the data on a different sheet. I want to be able to clear all the data on the second sheet before it starts copying the data over.
Any ideas on how I can fix this?
Thanks,
Any ideas on how I can fix this?
Code:
Sub ConvertSingleLine()
Dim x As Long
Dim Sheet1TotalRows As Long
Dim Sheet2TotalRows As Long
Dim Sheet2Row As Long
Sheet1TotalRows = Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row
Sheet2TotalRows = Sheets(2).Cells.SpecialCells(xlCellTypeLastCell).Row
Sheets(2).Range("A2:AH" & Sheet2TotalRows).Clear
Sheet2Row = 1
For x = 1 To Sheet1TotalRows
Select Case Sheets(1).Cells(x, 1).Value
Case " Tier: Core Certified", " Tier: SPG Lite", " Tier: SPG Classic", " Tier: Service Lite"
Sheet2Row = Sheet2Row + 1
Sheets(2).Cells(Sheet2Row, 2).Value = Sheets(1).Cells(x, 1).Value
Sheets(2).Cells(Sheet2Row, 1).Value = Sheets(1).Cells(x - 1, 1).Value
Sheets(2).Cells(Sheet2Row, 3).Value = Sheets(1).Cells(x - 1, 2).Value
Sheets(2).Cells(Sheet2Row, 4).Value = Sheets(1).Cells(x - 1, 3).Value
Sheets(2).Cells(Sheet2Row, 5).Value = Sheets(1).Cells(x - 1, 4).Value
Sheets(2).Cells(Sheet2Row, 6).Value = Sheets(1).Cells(x - 1, 5).Value
Sheets(2).Cells(Sheet2Row, 7).Value = Sheets(1).Cells(x, 2).Value
Sheets(2).Cells(Sheet2Row, 8).Value = Sheets(1).Cells(x, 3).Value
Sheets(2).Cells(Sheet2Row, 9).Value = Sheets(1).Cells(x, 4).Value
Sheets(2).Cells(Sheet2Row, 10).Value = Sheets(1).Cells(x, 5).Value
Sheets(2).Cells(Sheet2Row, 11).Value = Sheets(1).Cells(x + 1, 2).Value
Sheets(2).Cells(Sheet2Row, 12).Value = Sheets(1).Cells(x + 1, 3).Value
Sheets(2).Cells(Sheet2Row, 13).Value = Sheets(1).Cells(x + 1, 4).Value
Sheets(2).Cells(Sheet2Row, 14).Value = Sheets(1).Cells(x + 1, 5).Value
Sheets(2).Cells(Sheet2Row, 15).Value = Sheets(1).Cells(x + 2, 2).Value
Sheets(2).Cells(Sheet2Row, 16).Value = Sheets(1).Cells(x + 2, 3).Value
Sheets(2).Cells(Sheet2Row, 17).Value = Sheets(1).Cells(x + 2, 4).Value
Sheets(2).Cells(Sheet2Row, 18).Value = Sheets(1).Cells(x + 2, 5).Value
Sheets(2).Cells(Sheet2Row, 19).Value = Sheets(1).Cells(x + 3, 2).Value
Sheets(2).Cells(Sheet2Row, 20).Value = Sheets(1).Cells(x + 3, 3).Value
Sheets(2).Cells(Sheet2Row, 21).Value = Sheets(1).Cells(x + 3, 4).Value
Sheets(2).Cells(Sheet2Row, 22).Value = Sheets(1).Cells(x + 3, 5).Value
Sheets(2).Cells(Sheet2Row, 23).Value = Sheets(1).Cells(x + 4, 2).Value
Sheets(2).Cells(Sheet2Row, 24).Value = Sheets(1).Cells(x + 4, 3).Value
Sheets(2).Cells(Sheet2Row, 25).Value = Sheets(1).Cells(x + 4, 4).Value
Sheets(2).Cells(Sheet2Row, 26).Value = Sheets(1).Cells(x + 4, 5).Value
Sheets(2).Cells(Sheet2Row, 27).Value = Sheets(1).Cells(x + 5, 2).Value
Sheets(2).Cells(Sheet2Row, 28).Value = Sheets(1).Cells(x + 5, 3).Value
Sheets(2).Cells(Sheet2Row, 29).Value = Sheets(1).Cells(x + 5, 4).Value
Sheets(2).Cells(Sheet2Row, 30).Value = Sheets(1).Cells(x + 5, 5).Value
Sheets(2).Cells(Sheet2Row, 31).Value = Sheets(1).Cells(x + 6, 2).Value
Sheets(2).Cells(Sheet2Row, 32).Value = Sheets(1).Cells(x + 6, 3).Value
Sheets(2).Cells(Sheet2Row, 33).Value = Sheets(1).Cells(x + 6, 4).Value
Sheets(2).Cells(Sheet2Row, 34).Value = Sheets(1).Cells(x + 6, 5).Value
End Select
Next
End Sub
Thanks,