30 country dashboard question

[bigcountry]

I have a monthly report that I inherited that I send to my bosses. It includes all 30 countries that we do business in Europe. I have sales data for the past 2 years. Currently this report consists of 30 tabs at the bottom for each country. One column for sales, sales forecast, and sales forecast hit %. It's a visual disaster.


I've gone through the dashboard post on the site, but with 30 countries - can anyone point me in the right direction as to how to clean this mess up?

 

[bobhc]

Good day bigcountry


You could use the consolidation tool in the data tab, choose Data | Consolidate if that does not please then use some VBA

This macro creates a new sheet at the front of your workbook with all data from all sheets on it, ALL DATA on all sheets must be set out the same ie. the structure of each worksheet must be identical

[pre]

Sub Combine()
Dim J As Integer

On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"

' copy headings
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")

' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets

' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

[/pre]