Stephen Spittal
New Member
Good Morning form the UK.
I have a spreadsheet that is a download of a Database and I require to manipulate this to feed into a dashboard data table. I use the code below to do the manipulation but it takes forever and I do not know if or how to make it faster. can anyone help?
I have a spreadsheet that is a download of a Database and I require to manipulate this to feed into a dashboard data table. I use the code below to do the manipulation but it takes forever and I do not know if or how to make it faster. can anyone help?
Code:
Sub DashboardCSV()
Windows("dashboard_v.csv").Activate
Sheets("dashboard_v").Select
Dim lr As Long, i As Long
Dim FM As Integer
Dim MM As Integer
Dim AM As Integer
Dim NM As Integer
FM = 2
MM = 4
AM = 6
NM = 12
lr = Range("O" & Rows.Count).End(xlUp).Row
For i = lr To 1 Step -1
If Range("O" & i).Value = "-" And Range("O" & i).Offset(0, -9).Value = "MM" And Range("O" & i).Offset(0, -5).Value = "Impact Assessment" And Range("O" & i).Offset(0, -4).Value = "PENDING" Then
For y = 1 To MM
Range("O" & i).EntireRow.Copy
Range("O" & i).EntireRow.Insert shift:=xlShiftDown
Next
Range("O" & i).Value = "03DZ - BZB"
Range("o" & i).Offset(0, 2).Value = "0"
Range("o" & i).Offset(0, 6).Value = "0"
Range("O" & i).Offset(1).Value = "04DZ - BZB"
Range("O" & i).Offset(1, 2).Value = "0"
Range("O" & i).Offset(1, 6).Value = "0"
Range("O" & i).Offset(2).Value = "09DZ - BZE"
Range("O" & i).Offset(2, 2).Value = "0"
Range("O" & i).Offset(2, 6).Value = "0"
Range("O" & i).Offset(3).Value = "12DZ - BZH"
Range("O" & i).Offset(3, 2).Value = "0"
Range("O" & i).Offset(3, 6).Value = "0"
Range("O" & i).Offset(4).Value = "***"
Else
If Range("O" & i).Value = "-" And Range("O" & i).Offset(0, -9).Value = "AM" And Range("O" & i).Offset(0, -5).Value = "Impact Assessment" And Range("O" & i).Offset(0, -4).Value = "PENDING" Then
For y = 1 To AM
Range("O" & i).EntireRow.Copy
Range("O" & i).EntireRow.Insert shift:=xlShiftDown
Next
Range("O" & i).Value = "05DZ - BZC"
Range("O" & i).Offset(0, 2).Value = "0"
Range("O" & i).Offset(0, 6).Value = "0"
Range("O" & i).Offset(1).Value = "06DZ - BZC"
Range("O" & i).Offset(1, 2).Value = "0"
Range("O" & i).Offset(1, 6).Value = "0"
Range("O" & i).Offset(2).Value = "07DZ - BZD"
Range("O" & i).Offset(2, 2).Value = "0"
Range("O" & i).Offset(2, 6).Value = "0"
Range("O" & i).Offset(3).Value = "08DZ - BZD"
Range("O" & i).Offset(3, 2).Value = "0"
Range("O" & i).Offset(3, 6).Value = "0"
Range("O" & i).Offset(4).Value = "10DZ - BZF"
Range("O" & i).Offset(4, 2).Value = "0"
Range("O" & i).Offset(4, 6).Value = "0"
Range("O" & i).Offset(5).Value = "11DZ - BZG"
Range("O" & i).Offset(5, 2).Value = "0"
Range("O" & i).Offset(5, 6).Value = "0"
Range("O" & i).Offset(6).Value = "***"
Else
If Range("O" & i).Value = "-" And Range("O" & i).Offset(0, -9).Value = "FM" And Range("O" & i).Offset(0, -5).Value = "Impact Assessment" And Range("O" & i).Offset(0, -4).Value = "PENDING" Then
For y = 1 To FM
Range("O" & i).EntireRow.Copy
Range("O" & i).EntireRow.Insert shift:=xlShiftDown
Next
Range("O" & i).Value = "01DZ - BZA"
Range("O" & i).Offset(0, 2).Value = "0"
Range("O" & i).Offset(0, 6).Value = "0"
Range("O" & i).Offset(1).Value = "02DZ - BZA"
Range("O" & i).Offset(1, 2).Value = "0"
Range("O" & i).Offset(1, 6).Value = "0"
Range("O" & i).Offset(2).Value = "***"
Else
If Range("O" & i).Value = "-" And Range("O" & i).Offset(0, -9).Value = "NM" And Range("O" & i).Offset(0, -5).Value = "Impact Assessment" And Range("O" & i).Offset(0, -4).Value = "PENDING" Then
For y = 1 To NM
Range("O" & i).EntireRow.Copy
Range("O" & i).EntireRow.Insert shift:=xlShiftDown
Next
Range("O" & i).Value = "01DZ - BZA"
Range("O" & i).Offset(0, 2).Value = "0"
Range("O" & i).Offset(0, 6).Value = "0"
Range("O" & i).Offset(1).Value = "02DZ - BZA"
Range("O" & i).Offset(1, 2).Value = "0"
Range("O" & i).Offset(1, 6).Value = "0"
Range("O" & i).Offset(2).Value = "03DZ - BZB"
Range("O" & i).Offset(2, 2).Value = "0"
Range("O" & i).Offset(2, 6).Value = "0"
Range("O" & i).Offset(3).Value = "04DZ - BZB"
Range("O" & i).Offset(3, 2).Value = "0"
Range("O" & i).Offset(3, 6).Value = "0"
Range("O" & i).Offset(4).Value = "05DZ - BZC"
Range("O" & i).Offset(4, 2).Value = "0"
Range("O" & i).Offset(4, 6).Value = "0"
Range("O" & i).Offset(5).Value = "06DZ - BZC"
Range("O" & i).Offset(5, 2).Value = "0"
Range("O" & i).Offset(5, 6).Value = "0"
Range("O" & i).Offset(6).Value = "07DZ - BZD"
Range("O" & i).Offset(6, 2).Value = "0"
Range("O" & i).Offset(6, 6).Value = "0"
Range("O" & i).Offset(7).Value = "08DZ - BZD"
Range("O" & i).Offset(7, 2).Value = "0"
Range("O" & i).Offset(7, 6).Value = "0"
Range("O" & i).Offset(8).Value = "09DZ - BZE"
Range("O" & i).Offset(8, 2).Value = "0"
Range("O" & i).Offset(8, 6).Value = "0"
Range("O" & i).Offset(9).Value = "10DZ - BZF"
Range("O" & i).Offset(9, 2).Value = "0"
Range("O" & i).Offset(9, 6).Value = "0"
Range("O" & i).Offset(10).Value = "11DZ - BZG"
Range("O" & i).Offset(10, 2).Value = "0"
Range("O" & i).Offset(10, 6).Value = "0"
Range("O" & i).Offset(11).Value = "12DZ - BZH"
Range("O" & i).Offset(11, 2).Value = "0"
Range("O" & i).Offset(11, 6).Value = "0"
Range("O" & i).Offset(12).Value = "***"
End If
End If
End If
End If
Next
Windows("Stage 2 Interactive Change Management Dashboard.xlsm").Activate
ActiveWindow.WindowState = xlMaximized
MsgBox ("dashboard_v.csv Updated")
End Sub