Hi,
Im having a huge macro code and it might take hours to run it. The master workbook from where the data is copied is huge as well but still I hope there is some tricks to make the code run faster.
Any ideas? Code below.
Im having a huge macro code and it might take hours to run it. The master workbook from where the data is copied is huge as well but still I hope there is some tricks to make the code run faster.
Any ideas? Code below.
VBA Code:
Sub copyall()
Dim wb As New Workbook, rowToCopy As Integer
Dim lRow As Integer, nRow As Integer, rowno As Integer, colno As Integer
'Create workbook with name xxx in which data will be copied. Keep the file open.
Set wb = Workbooks("SalesAll.xlsx")
lRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
nRow = wb.Sheets("Sales").Cells(Rows.Count, 1).End(xlUp).Row + 1
rowToCopy = nRow
For rowno = 2 To lRow
' Copy sales Total
If (ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Close (won)" Or ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Close (part-won)") _
And ThisWorkbook.Sheets("Sheet1").Range("L" & rowno) > 0 Then
For colno = 92 To 92
If ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno) > 0 Then
ThisWorkbook.Sheets("Sheet1").Range("A" & rowno).Copy wb.Sheets("Sales").Range("A" & rowToCopy) 'To copy no
ThisWorkbook.Sheets("Sheet1").Range("L" & rowno).Copy wb.Sheets("Sales").Range("B" & rowToCopy) 'To copy sales person 1 name
ThisWorkbook.Sheets("Sheet1").Range("E" & rowno).Copy wb.Sheets("Sales").Range("M" & rowToCopy) 'To copy customer name
ThisWorkbook.Sheets("Sheet1").Range("F" & rowno).Copy wb.Sheets("Sales").Range("N" & rowToCopy) 'To copy legal number
ThisWorkbook.Sheets("Sheet1").Range("R" & rowno).Copy wb.Sheets("Sales").Range("G" & rowToCopy) 'To copy status
ThisWorkbook.Sheets("Sheet1").Range("Q" & rowno).Copy wb.Sheets("Sales").Range("H" & rowToCopy) 'To copy sales type
ThisWorkbook.Sheets("Sheet1").Range("G" & rowno).Copy wb.Sheets("Sales").Range("O" & rowToCopy) 'To copy Group
ThisWorkbook.Sheets("Sheet1").Range("H" & rowno).Copy wb.Sheets("Sales").Range("P" & rowToCopy) 'To copy Group Name
ThisWorkbook.Sheets("Sheet1").Cells(1, colno).Copy wb.Sheets("Sales").Range("L" & rowToCopy) 'To copy product name
ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno).Copy
wb.Sheets("Sales").Range("F" & rowToCopy).PasteSpecial xlPasteValues 'To copy product value
rowToCopy = rowToCopy + 1
End If
Next
End If
'Copy sales sales person 1
If (ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Close (won)" Or ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Close (part-won)") _
And ThisWorkbook.Sheets("Sheet1").Range("L" & rowno) > 0 Then
For colno = 98 To 100
If ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno) > 0 Then
ThisWorkbook.Sheets("Sheet1").Range("A" & rowno).Copy wb.Sheets("Sales").Range("A" & rowToCopy) 'To copy no
ThisWorkbook.Sheets("Sheet1").Range("L" & rowno).Copy wb.Sheets("Sales").Range("B" & rowToCopy) 'To copy sales person 1 name
ThisWorkbook.Sheets("Sheet1").Range("E" & rowno).Copy wb.Sheets("Sales").Range("M" & rowToCopy) 'To copy customer name
ThisWorkbook.Sheets("Sheet1").Range("F" & rowno).Copy wb.Sheets("Sales").Range("N" & rowToCopy) 'To copy legal number
ThisWorkbook.Sheets("Sheet1").Range("R" & rowno).Copy wb.Sheets("Sales").Range("G" & rowToCopy) 'To copy status
ThisWorkbook.Sheets("Sheet1").Range("Q" & rowno).Copy wb.Sheets("Sales").Range("H" & rowToCopy) 'To copy sales type
ThisWorkbook.Sheets("Sheet1").Range("G" & rowno).Copy wb.Sheets("Sales").Range("O" & rowToCopy) 'To copy Group
ThisWorkbook.Sheets("Sheet1").Range("H" & rowno).Copy wb.Sheets("Sales").Range("P" & rowToCopy) 'To copy Group Name
ThisWorkbook.Sheets("Sheet1").Cells(1, colno).Copy wb.Sheets("Sales").Range("L" & rowToCopy) 'To copy product name
ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno).Copy
wb.Sheets("Sales").Range("F" & rowToCopy).PasteSpecial xlPasteValues 'To copy product value
rowToCopy = rowToCopy + 1
End If
Next
End If
'Copy sales sales person 2
If (ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Close (won)" Or ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Close (part-won)") _
And ThisWorkbook.Sheets("Sheet1").Range("M" & rowno) > 0 Then
For colno = 101 To 103
If ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno) > 0 Then
ThisWorkbook.Sheets("Sheet1").Range("A" & rowno).Copy wb.Sheets("Sales").Range("A" & rowToCopy) 'To copy no
ThisWorkbook.Sheets("Sheet1").Range("M" & rowno).Copy wb.Sheets("Sales").Range("B" & rowToCopy) 'To copy sales person 2 name
ThisWorkbook.Sheets("Sheet1").Range("E" & rowno).Copy wb.Sheets("Sales").Range("M" & rowToCopy) 'To copy customer name
ThisWorkbook.Sheets("Sheet1").Range("F" & rowno).Copy wb.Sheets("Sales").Range("N" & rowToCopy) 'To copy legal number
ThisWorkbook.Sheets("Sheet1").Range("R" & rowno).Copy wb.Sheets("Sales").Range("G" & rowToCopy) 'To copy status
ThisWorkbook.Sheets("Sheet1").Range("Q" & rowno).Copy wb.Sheets("Sales").Range("H" & rowToCopy) 'To copy sales type
ThisWorkbook.Sheets("Sheet1").Range("G" & rowno).Copy wb.Sheets("Sales").Range("O" & rowToCopy) 'To copy Group
ThisWorkbook.Sheets("Sheet1").Range("H" & rowno).Copy wb.Sheets("Sales").Range("P" & rowToCopy) 'To copy Group Name
ThisWorkbook.Sheets("Sheet1").Cells(1, colno).Copy wb.Sheets("Sales").Range("L" & rowToCopy) 'To copy product name
ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno).Copy
wb.Sheets("Sales").Range("F" & rowToCopy).PasteSpecial xlPasteValues 'To copy product value
rowToCopy = rowToCopy + 1
End If
Next
End If
'Copy lob's sales person 1
If (ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Close (won)" Or ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Close (part-won)") _
And ThisWorkbook.Sheets("Sheet1").Range("L" & rowno) > 0 Then
For colno = 104 To 109
If ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno) > 0 Then
ThisWorkbook.Sheets("Sheet1").Range("A" & rowno).Copy wb.Sheets("Sales").Range("A" & rowToCopy) 'To copy no
ThisWorkbook.Sheets("Sheet1").Range("L" & rowno).Copy wb.Sheets("Sales").Range("B" & rowToCopy) 'To copy sales person 1 name
ThisWorkbook.Sheets("Sheet1").Range("E" & rowno).Copy wb.Sheets("Sales").Range("M" & rowToCopy) 'To copy customer name
ThisWorkbook.Sheets("Sheet1").Range("F" & rowno).Copy wb.Sheets("Sales").Range("N" & rowToCopy) 'To copy legal number
ThisWorkbook.Sheets("Sheet1").Range("G" & rowno).Copy wb.Sheets("Sales").Range("O" & rowToCopy) 'To copy Group
ThisWorkbook.Sheets("Sheet1").Range("H" & rowno).Copy wb.Sheets("Sales").Range("P" & rowToCopy) 'To copy Group Name
ThisWorkbook.Sheets("Sheet1").Range("R" & rowno).Copy wb.Sheets("Sales").Range("G" & rowToCopy) 'To copy status
ThisWorkbook.Sheets("Sheet1").Range("Q" & rowno).Copy wb.Sheets("Sales").Range("H" & rowToCopy) 'To copy sales type
ThisWorkbook.Sheets("Sheet1").Cells(1, colno).Copy wb.Sheets("Sales").Range("L" & rowToCopy) 'To copy product name
ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno).Copy
wb.Sheets("Sales").Range("F" & rowToCopy).PasteSpecial xlPasteValues 'To copy product value
rowToCopy = rowToCopy + 1
End If
Next
End If
'Copy lob's sales person 2
If (ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Close (won)" Or ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Close (part-won)") _
And ThisWorkbook.Sheets("Sheet1").Range("M" & rowno) > 0 Then
For colno = 110 To 115
If ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno) > 0 Then
ThisWorkbook.Sheets("Sheet1").Range("A" & rowno).Copy wb.Sheets("Sales").Range("A" & rowToCopy) 'To copy no
ThisWorkbook.Sheets("Sheet1").Range("M" & rowno).Copy wb.Sheets("Sales").Range("B" & rowToCopy) 'To copy sales person 2 name
ThisWorkbook.Sheets("Sheet1").Range("E" & rowno).Copy wb.Sheets("Sales").Range("M" & rowToCopy) 'To copy customer name
ThisWorkbook.Sheets("Sheet1").Range("F" & rowno).Copy wb.Sheets("Sales").Range("N" & rowToCopy) 'To copy legal number
ThisWorkbook.Sheets("Sheet1").Range("G" & rowno).Copy wb.Sheets("Sales").Range("O" & rowToCopy) 'To copy Group
ThisWorkbook.Sheets("Sheet1").Range("H" & rowno).Copy wb.Sheets("Sales").Range("P" & rowToCopy) 'To copy Group Name
ThisWorkbook.Sheets("Sheet1").Range("R" & rowno).Copy wb.Sheets("Sales").Range("G" & rowToCopy) 'To copy status
ThisWorkbook.Sheets("Sheet1").Range("Q" & rowno).Copy wb.Sheets("Sales").Range("H" & rowToCopy) 'To copy sales type
ThisWorkbook.Sheets("Sheet1").Cells(1, colno).Copy wb.Sheets("Sales").Range("L" & rowToCopy) 'To copy product name
ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno).Copy
wb.Sheets("Sales").Range("F" & rowToCopy).PasteSpecial xlPasteValues 'To copy product value
rowToCopy = rowToCopy + 1
End If
Next
End If
'Copy defence
If (ThisWorkbook.Sheets("Sheet1").Range("Q" & rowno) = "Nykyinen asiakas, puolustus" Or ThisWorkbook.Sheets("Sheet1").Range("Q" & rowno) = "Nykyinen asiakas, puolustus + lisämyynti") _
And ThisWorkbook.Sheets("Sheet1").Range("L" & rowno) > 0 Then
For colno = 41 To 41
If ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno) > 0 Then
ThisWorkbook.Sheets("Sheet1").Range("A" & rowno).Copy wb.Sheets("Sales").Range("A" & rowToCopy) 'To copy no
ThisWorkbook.Sheets("Sheet1").Range("L" & rowno).Copy wb.Sheets("Sales").Range("B" & rowToCopy) 'To copy sales person 1 name
ThisWorkbook.Sheets("Sheet1").Range("E" & rowno).Copy wb.Sheets("Sales").Range("M" & rowToCopy) 'To copy customer name
ThisWorkbook.Sheets("Sheet1").Range("F" & rowno).Copy wb.Sheets("Sales").Range("N" & rowToCopy) 'To copy legal number
ThisWorkbook.Sheets("Sheet1").Range("G" & rowno).Copy wb.Sheets("Sales").Range("O" & rowToCopy) 'To copy Group
ThisWorkbook.Sheets("Sheet1").Range("H" & rowno).Copy wb.Sheets("Sales").Range("P" & rowToCopy) 'To copy Group Name
ThisWorkbook.Sheets("Sheet1").Range("R" & rowno).Copy wb.Sheets("Sales").Range("G" & rowToCopy) 'To copy status
ThisWorkbook.Sheets("Sheet1").Range("Q" & rowno).Copy wb.Sheets("Sales").Range("H" & rowToCopy) 'To copy sales type
ThisWorkbook.Sheets("Sheet1").Cells(1, colno).Copy wb.Sheets("Sales").Range("L" & rowToCopy) 'To copy product name
ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno).Copy
wb.Sheets("Sales").Range("F" & rowToCopy).PasteSpecial xlPasteValues 'To copy product value
rowToCopy = rowToCopy + 1
End If
Next
End If
'copy offers total
If (ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Negotiate" Or ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Close (lost)") And ThisWorkbook.Sheets("Sheet1").Range("L" & rowno) > 0 Then
For colno = 71 To 72
If ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno) > 0 Then
ThisWorkbook.Sheets("Sheet1").Range("A" & rowno).Copy wb.Sheets("Sales").Range("A" & rowToCopy) 'To copy no
ThisWorkbook.Sheets("Sheet1").Range("L" & rowno).Copy wb.Sheets("Sales").Range("B" & rowToCopy) 'To copy sales person 1 name
ThisWorkbook.Sheets("Sheet1").Range("E" & rowno).Copy wb.Sheets("Sales").Range("M" & rowToCopy) 'To copy customer name
ThisWorkbook.Sheets("Sheet1").Range("F" & rowno).Copy wb.Sheets("Sales").Range("N" & rowToCopy) 'To copy legal number
ThisWorkbook.Sheets("Sheet1").Range("G" & rowno).Copy wb.Sheets("Sales").Range("O" & rowToCopy) 'To copy Group
ThisWorkbook.Sheets("Sheet1").Range("H" & rowno).Copy wb.Sheets("Sales").Range("P" & rowToCopy) 'To copy Group Name
ThisWorkbook.Sheets("Sheet1").Range("R" & rowno).Copy wb.Sheets("Sales").Range("G" & rowToCopy) 'To copy status
ThisWorkbook.Sheets("Sheet1").Range("Q" & rowno).Copy wb.Sheets("Sales").Range("H" & rowToCopy) 'To copy sales type
ThisWorkbook.Sheets("Sheet1").Cells(1, colno).Copy wb.Sheets("Sales").Range("L" & rowToCopy) 'To copy product name
ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno).Copy
wb.Sheets("Sales").Range("F" & rowToCopy).PasteSpecial xlPasteValues 'To copy product value
rowToCopy = rowToCopy + 1
End If
Next
End If
'copy offers sales person 2
If (ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Negotiate" Or ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Close (lost)") And ThisWorkbook.Sheets("Sheet1").Range("M" & rowno) > 0 Then
For colno = 73 To 73
If ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno) > 0 Then
ThisWorkbook.Sheets("Sheet1").Range("A" & rowno).Copy wb.Sheets("Sales").Range("A" & rowToCopy) 'To copy no
ThisWorkbook.Sheets("Sheet1").Range("M" & rowno).Copy wb.Sheets("Sales").Range("B" & rowToCopy) 'To copy sales person 2 name
ThisWorkbook.Sheets("Sheet1").Range("E" & rowno).Copy wb.Sheets("Sales").Range("M" & rowToCopy) 'To copy customer name
ThisWorkbook.Sheets("Sheet1").Range("F" & rowno).Copy wb.Sheets("Sales").Range("N" & rowToCopy) 'To copy legal number
ThisWorkbook.Sheets("Sheet1").Range("G" & rowno).Copy wb.Sheets("Sales").Range("O" & rowToCopy) 'To copy Group
ThisWorkbook.Sheets("Sheet1").Range("H" & rowno).Copy wb.Sheets("Sales").Range("P" & rowToCopy) 'To copy Group Name
ThisWorkbook.Sheets("Sheet1").Range("R" & rowno).Copy wb.Sheets("Sales").Range("G" & rowToCopy) 'To copy status
ThisWorkbook.Sheets("Sheet1").Range("Q" & rowno).Copy wb.Sheets("Sales").Range("H" & rowToCopy) 'To copy sales type
ThisWorkbook.Sheets("Sheet1").Cells(1, colno).Copy wb.Sheets("Sales").Range("L" & rowToCopy) 'To copy product name
ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno).Copy
wb.Sheets("Sales").Range("F" & rowToCopy).PasteSpecial xlPasteValues 'To copy product value
rowToCopy = rowToCopy + 1
End If
Next
End If
'copy offers lob's sales person 1
If (ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Negotiate" Or ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Close (lost)") And ThisWorkbook.Sheets("Sheet1").Range("L" & rowno) > 0 Then
For colno = 74 To 79
If ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno) > 0 Then
ThisWorkbook.Sheets("Sheet1").Range("A" & rowno).Copy wb.Sheets("Sales").Range("A" & rowToCopy) 'To copy no
ThisWorkbook.Sheets("Sheet1").Range("L" & rowno).Copy wb.Sheets("Sales").Range("B" & rowToCopy) 'To copy sales person 1 name
ThisWorkbook.Sheets("Sheet1").Range("E" & rowno).Copy wb.Sheets("Sales").Range("M" & rowToCopy) 'To copy customer name
ThisWorkbook.Sheets("Sheet1").Range("F" & rowno).Copy wb.Sheets("Sales").Range("N" & rowToCopy) 'To copy legal number
ThisWorkbook.Sheets("Sheet1").Range("G" & rowno).Copy wb.Sheets("Sales").Range("O" & rowToCopy) 'To copy Group
ThisWorkbook.Sheets("Sheet1").Range("H" & rowno).Copy wb.Sheets("Sales").Range("P" & rowToCopy) 'To copy Group Name
ThisWorkbook.Sheets("Sheet1").Range("R" & rowno).Copy wb.Sheets("Sales").Range("G" & rowToCopy) 'To copy status
ThisWorkbook.Sheets("Sheet1").Range("Q" & rowno).Copy wb.Sheets("Sales").Range("H" & rowToCopy) 'To copy sales type
ThisWorkbook.Sheets("Sheet1").Cells(1, colno).Copy wb.Sheets("Sales").Range("L" & rowToCopy) 'To copy product name
ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno).Copy
wb.Sheets("Sales").Range("F" & rowToCopy).PasteSpecial xlPasteValues 'To copy product value
rowToCopy = rowToCopy + 1
End If
Next
End If
'copy offers lob's sales person 2
If (ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Negotiate" Or ThisWorkbook.Sheets("Sheet1").Range("R" & rowno) = "Close (lost)") And ThisWorkbook.Sheets("Sheet1").Range("M" & rowno) > 0 Then
For colno = 80 To 85
If ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno) > 0 Then
ThisWorkbook.Sheets("Sheet1").Range("A" & rowno).Copy wb.Sheets("Sales").Range("A" & rowToCopy) 'To copy no
ThisWorkbook.Sheets("Sheet1").Range("M" & rowno).Copy wb.Sheets("Sales").Range("B" & rowToCopy) 'To copy sales person 2 name
ThisWorkbook.Sheets("Sheet1").Range("E" & rowno).Copy wb.Sheets("Sales").Range("M" & rowToCopy) 'To copy customer name
ThisWorkbook.Sheets("Sheet1").Range("F" & rowno).Copy wb.Sheets("Sales").Range("N" & rowToCopy) 'To copy legal number
ThisWorkbook.Sheets("Sheet1").Range("G" & rowno).Copy wb.Sheets("Sales").Range("O" & rowToCopy) 'To copy Group
ThisWorkbook.Sheets("Sheet1").Range("H" & rowno).Copy wb.Sheets("Sales").Range("P" & rowToCopy) 'To copy Group Name
ThisWorkbook.Sheets("Sheet1").Range("R" & rowno).Copy wb.Sheets("Sales").Range("G" & rowToCopy) 'To copy status
ThisWorkbook.Sheets("Sheet1").Range("Q" & rowno).Copy wb.Sheets("Sales").Range("H" & rowToCopy) 'To copy sales type
ThisWorkbook.Sheets("Sheet1").Cells(1, colno).Copy wb.Sheets("Sales").Range("L" & rowToCopy) 'To copy product name
ThisWorkbook.Sheets("Sheet1").Cells(rowno, colno).Copy
wb.Sheets("Sales").Range("F" & rowToCopy).PasteSpecial xlPasteValues 'To copy product value
rowToCopy = rowToCopy + 1
End If
Next
End If
Next
End Sub