Hi All,
I have an issue that I hope someone can help with!
I have multiple workbooks sitting on a SharePoint site that sales reps will use to track pipeline. Each manager also has a workbook that links to their teams workbooks so they can get a complete view of their teams pipeline and closed deals. I need to be able to consolidate each team members pipeline onto one sheet for reporting and dashboard purposes... which I was able to do using the VBA below. Unfortunately, I cannot report off of the consolidated data as it clears every time it's refreshed, causing the dashboards and reports to stay stagnant and not update with current data.
Any suggestions on how to achieve what I am looking for? Any help would be greatly appreciated as this stop-gap solution needs to be up and running quickly.
Thanks you!
Sub Consolidate_Sheets_Click()
Dim wsTest As Worksheet
'check if sheet "East Team Opportunities" already exist
Const strSheetName As String = "East Team Opportunities"
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
If wsTest Is Nothing Then
Worksheets.Add.Name = strSheetName
End If
With Sheets("East Team Opportunities")
.UsedRange.ClearContents
.Range("A1:X1").Value = Array("Sheet Name", "Project Name", "Account Name", "Estimated Amount", "Close Date", "Sales Stage", "Risk Indicator", "Last Contact Date", "Description", "Deal Status", "Lead Region", "Signing Type", "Main Competitor", "Lead Source", "Primary Win / Loss Reason", "Next Steps", "Issues & Risks", "Deal Registration #", "Distributor", "Distributor Contact", "Reseller", "Reseller Contact", "User Name", "Created Date", "Modified Date")
For Each Sh In Sheets
With Sh
If .Name <> "East Team Opportunties" And .Name <> "Dashboard" And .Name <> "Andreone_Data" And .Name <> "Churchville_Data" And .Name <> "Hewitt_Data" And .Name <> "Johnson_Data" And .Name <> "Keener_Data" And .Name <> "Riley_Data" And .Name <> "Settings" Then
Rng = .Cells.Find("*", , , , xlByRows, xlPrevious).Row - 1
NR = Sheets("East Team Opportunities").Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1
If Rng > 0 Then
Sheets("East Team Opportunities").Cells(NR, 1).Resize(Rng) = .Name
Sheets("East Team Opportunities").Cells(NR, 2).Resize(Rng, 24) = .Range("A2").Resize(Rng, 24).Value
End If
End If
End With
Next
.Range("C2:C" & .Rows.Count).SpecialCells(4).EntireRow.Delete
.Columns("A:Z").EntireColumn.AutoFit
End With
End Sub
I have an issue that I hope someone can help with!
I have multiple workbooks sitting on a SharePoint site that sales reps will use to track pipeline. Each manager also has a workbook that links to their teams workbooks so they can get a complete view of their teams pipeline and closed deals. I need to be able to consolidate each team members pipeline onto one sheet for reporting and dashboard purposes... which I was able to do using the VBA below. Unfortunately, I cannot report off of the consolidated data as it clears every time it's refreshed, causing the dashboards and reports to stay stagnant and not update with current data.
Any suggestions on how to achieve what I am looking for? Any help would be greatly appreciated as this stop-gap solution needs to be up and running quickly.
Thanks you!
Sub Consolidate_Sheets_Click()
Dim wsTest As Worksheet
'check if sheet "East Team Opportunities" already exist
Const strSheetName As String = "East Team Opportunities"
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
If wsTest Is Nothing Then
Worksheets.Add.Name = strSheetName
End If
With Sheets("East Team Opportunities")
.UsedRange.ClearContents
.Range("A1:X1").Value = Array("Sheet Name", "Project Name", "Account Name", "Estimated Amount", "Close Date", "Sales Stage", "Risk Indicator", "Last Contact Date", "Description", "Deal Status", "Lead Region", "Signing Type", "Main Competitor", "Lead Source", "Primary Win / Loss Reason", "Next Steps", "Issues & Risks", "Deal Registration #", "Distributor", "Distributor Contact", "Reseller", "Reseller Contact", "User Name", "Created Date", "Modified Date")
For Each Sh In Sheets
With Sh
If .Name <> "East Team Opportunties" And .Name <> "Dashboard" And .Name <> "Andreone_Data" And .Name <> "Churchville_Data" And .Name <> "Hewitt_Data" And .Name <> "Johnson_Data" And .Name <> "Keener_Data" And .Name <> "Riley_Data" And .Name <> "Settings" Then
Rng = .Cells.Find("*", , , , xlByRows, xlPrevious).Row - 1
NR = Sheets("East Team Opportunities").Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1
If Rng > 0 Then
Sheets("East Team Opportunities").Cells(NR, 1).Resize(Rng) = .Name
Sheets("East Team Opportunities").Cells(NR, 2).Resize(Rng, 24) = .Range("A2").Resize(Rng, 24).Value
End If
End If
End With
Next
.Range("C2:C" & .Rows.Count).SpecialCells(4).EntireRow.Delete
.Columns("A:Z").EntireColumn.AutoFit
End With
End Sub
