Hi All,
I have a module written to pull data from one sheet (Raw Data) and into another (Opportunities). But I am having trouble with the last piece. I THINK I need to group by Column B in Raw Data - but I still want to sum by Column C in Raw Data.
Not sure if that is correct, but I was able to create what I wanted in a pivot table (example pasted below). I am trying to figure out how to apply this to my module. Any help or insight would be much appreciated!
Thank you!!
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 0ff]Raw Data info for reference:[/COLOR]
Account Name = Column B
Opportunity Name = Column C
R Type = Column M
CM Prod = Column Q
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 0cd]Pivot table creation:[/COLOR]
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 0ff]Module currently built:[/COLOR]
Sub MoveDataToOpportunitiesSheet()
Dim rowCount, count, destinationCount, probability As Integer
Dim selectedPOD, rowPOD, rType As String
Set sourceSheet = ActiveWorkbook.Sheets("Raw Data")
Set currentSheet = ActiveWorkbook.Sheets("Opportunities")
' Clear Data from Sheet
currentSheet.Rows("6:" & currentSheet.Rows.count).ClearContents
' get the total rows present
rowCount = sourceSheet.UsedRange.Row + sourceSheet.UsedRange.Rows.count - 1
count = 2
destinationCount = 6
selectedPOD = cmbPOD2.Value
For i = count To rowCount
probability = sourceSheet.Cells(i, 11).Value
rowPOD = sourceSheet.Cells(i, 19).Value
rType = sourceSheet.Cells(i, 13).Value
If probability >= 75 And probability <= 100 And rowPOD = selectedPOD Then
currentSheet.Cells(destinationCount, 1).Value = sourceSheet.Cells(i, 2).Value
currentSheet.Cells(destinationCount, 2).Value = sourceSheet.Cells(i, 3).Value
currentSheet.Cells(destinationCount, 3).Value = sourceSheet.Cells(i, 1).Value
currentSheet.Cells(destinationCount, 4).Value = sourceSheet.Cells(i, 12).Value
If rType = "New" Then
currentSheet.Cells(destinationCount, 5).Value = sourceSheet.Cells(i, 17).Value
currentSheet.Cells(destinationCount, 5).NumberFormat = "$#,##0.00"
Else
currentSheet.Cells(destinationCount, 5).Value = ""
End If
If rType = "Renewal" Then
currentSheet.Cells(destinationCount, 6).Value = sourceSheet.Cells(i, 17).Value
currentSheet.Cells(destinationCount, 6).NumberFormat = "$#,##0.00"
Else
currentSheet.Cells(destinationCount, 6).Value = ""
End If
If rType = "Carryover" Then
currentSheet.Cells(destinationCount, 7).Value = sourceSheet.Cells(i, 17).Value
currentSheet.Cells(destinationCount, 7).NumberFormat = "$#,##0.00"
Else
currentSheet.Cells(destinationCount, 7).Value = ""
End If
destinationCount = destinationCount + 1
End If
Next i
Range("A6:G6").Sort key1:=Range("B6"), order1:=xlAscending, Header:=xlNo
Columns("A:G").Select
Selection.EntireColumn.AutoFit
End Sub
I have a module written to pull data from one sheet (Raw Data) and into another (Opportunities). But I am having trouble with the last piece. I THINK I need to group by Column B in Raw Data - but I still want to sum by Column C in Raw Data.
Not sure if that is correct, but I was able to create what I wanted in a pivot table (example pasted below). I am trying to figure out how to apply this to my module. Any help or insight would be much appreciated!
Thank you!!
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 0ff]Raw Data info for reference:[/COLOR]
Account Name = Column B
Opportunity Name = Column C
R Type = Column M
CM Prod = Column Q
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 0cd]Pivot table creation:[/COLOR]
Sum of CM PROD | R Type | |||
Account Name | Opportunity Name | Carryover | New | Renewal |
Account 123 | Opportunity 123 | $218,446.00 | ||
Account 123b | Opportunity 123 | $81,548.00 | ||
Account 125 | Opportunity 125 | $91,536.00 | ||
Account 126 | Opportunity 126 | $242,450.00 | ||
Account 127 | Opportunity 127 | $43,630.00 | ||
Account 128 | Opportunity 128 | $62,120.00 | ||
Account 129 | Opportunity 129 | $105,682.00 |
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=000]#000[/URL] 0ff]Module currently built:[/COLOR]
Sub MoveDataToOpportunitiesSheet()
Dim rowCount, count, destinationCount, probability As Integer
Dim selectedPOD, rowPOD, rType As String
Set sourceSheet = ActiveWorkbook.Sheets("Raw Data")
Set currentSheet = ActiveWorkbook.Sheets("Opportunities")
' Clear Data from Sheet
currentSheet.Rows("6:" & currentSheet.Rows.count).ClearContents
' get the total rows present
rowCount = sourceSheet.UsedRange.Row + sourceSheet.UsedRange.Rows.count - 1
count = 2
destinationCount = 6
selectedPOD = cmbPOD2.Value
For i = count To rowCount
probability = sourceSheet.Cells(i, 11).Value
rowPOD = sourceSheet.Cells(i, 19).Value
rType = sourceSheet.Cells(i, 13).Value
If probability >= 75 And probability <= 100 And rowPOD = selectedPOD Then
currentSheet.Cells(destinationCount, 1).Value = sourceSheet.Cells(i, 2).Value
currentSheet.Cells(destinationCount, 2).Value = sourceSheet.Cells(i, 3).Value
currentSheet.Cells(destinationCount, 3).Value = sourceSheet.Cells(i, 1).Value
currentSheet.Cells(destinationCount, 4).Value = sourceSheet.Cells(i, 12).Value
If rType = "New" Then
currentSheet.Cells(destinationCount, 5).Value = sourceSheet.Cells(i, 17).Value
currentSheet.Cells(destinationCount, 5).NumberFormat = "$#,##0.00"
Else
currentSheet.Cells(destinationCount, 5).Value = ""
End If
If rType = "Renewal" Then
currentSheet.Cells(destinationCount, 6).Value = sourceSheet.Cells(i, 17).Value
currentSheet.Cells(destinationCount, 6).NumberFormat = "$#,##0.00"
Else
currentSheet.Cells(destinationCount, 6).Value = ""
End If
If rType = "Carryover" Then
currentSheet.Cells(destinationCount, 7).Value = sourceSheet.Cells(i, 17).Value
currentSheet.Cells(destinationCount, 7).NumberFormat = "$#,##0.00"
Else
currentSheet.Cells(destinationCount, 7).Value = ""
End If
destinationCount = destinationCount + 1
End If
Next i
Range("A6:G6").Sort key1:=Range("B6"), order1:=xlAscending, Header:=xlNo
Columns("A:G").Select
Selection.EntireColumn.AutoFit
End Sub