if column does not contain value then do not create pivot table

[florianvk]

Hello dear Excel users,

I wrote a macro that is creating 3 pivot tables, all in separate sheets, from the same table (table1) in the sheet RAWDATA.
The last pivot table must be created only when at least 1 row in table1 in column K is containing the word TRUCK.
If no rows exist with the word TRUCK in column K, the macro have to step over the part creating the pivot table and just select the sheet with the first pivot table called SUMMARY.

I have tried with 'Select Case' like the belwo example but I cannot define a range of cells (for example K2:K1000) with this function only 1 cell (K2).

Can sombody help me out?

Sheets("RAWDATA").Select
Select Case Range("K2").Value
Case "TRUCK"
GoTo 1111
Case Else
GoTo 2222


1111:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
:="", TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion14
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Vehicle Type Code")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("N or U")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Pod Name")
.Orientation = xlRowField
.Position = 2
End With
2222:
Sheets("SUMMARY").Select

 

[HalfAce]

Hi.
If you're simply trying to confirm the existence of the value 'truck' in Sheet "RAWDATA", column K, then try replacing:

Sheets("RAWDATA").Select
     Select Case Range("K2").Value
  Case "TRUCK"
  GoTo 1111
  Case Else
  GoTo 2222

with something like this:

If Application.WorksheetFunction.CountIf(Sheets("RAWDATA").Range("K:K"), "TRUCK") > 0 Then GoTo 1111 Else GoTo 2222

Hope it helps.

 

[florianvk]

Hi HalfAce,

Thanks a lot for your help. It works fine!

 

[HalfAce]

You're very welcome. Glad it helped.