Mario, vê se é isso que você precisa
Sub Preencher_relatorio()
Dim i As Integer
For i = 2 To 30
If (Sheets("Plan" & i).Range("q4").Value <> "") Then
Sheets("RELATORIO").Range("B4").End(xlToRight).Offset(0, 1).Value = Sheets("Plan" & i).Range("q4").Value
End If
If (Sheets("Plan" & i).Range("d25").Value <> "") Then
Sheets("RELATORIO").Range("C4").End(xlToRight).Offset(0, 1).Value = Sheets("Plan" & i).Range("d25").Value
End If
If (Sheets("Plan" & i).Range("d11").Value <> "") Then
Sheets("RELATORIO").Range("D4").End(xlToRight).Offset(0, 1).Value = Sheets("Plan" & i).Range("d11").Value
End If
If (Sheets("Plan" & i).Range("d33").Value <> "") Then
Sheets("RELATORIO").Range("E4").End(xlToRight).Offset(0, 1).Value = Sheets("Plan" & i).Range("d33").Value
End If
If (Sheets("Plan" & i).Range("i33").Value <> "") Then
Sheets("RELATORIO").Range("F4").End(xlToRight).Offset(0, 1).Value = Sheets("Plan" & i).Range("i33").Value
End If
If (Sheets("Plan" & i).Range("b33").Value <> "") Then
Sheets("RELATORIO").Range("G4").End(xlToRight).Offset(0, 1).Value = Sheets("Plan" & i).Range("b33").Value
End If
If (Sheets("Plan" & i).Range("n33").Value <> "") Then
Sheets("RELATORIO").Range("H4").End(xlToRight).Offset(0, 1).Value = Sheets("Plan" & i).Range("n33").Value
End If
Next i
End Sub