VBA Case Not Recognizing Blank cells

[sbpersson]

Good morning all,

So I'm just getting into VBA so I hope you all with cut me some slack in regards to this novice problem. I've written the following code to go through certain worksheets in a workbook, extract a certain range, and paste the data onto a separate sheet titled productivity. Unfortunately, I have no control over how this workbook is laid out (separate worksheets for each day of the month.... drives me crazy). I wrote this last week without a loop and it was very tedious. I'm going to need to write this for multiple functions so I really need this loop to work. My issue is that when it comes across blank cells it doesn't paste zeros into my productivity sheet. It seems to just skip them. This causes the dates not to lign up. Please help!

‪

Option Explicit
Private Sub InboundProd()
Dim ws As Worksheet
Dim lst As Long
Dim second As Long
 
For Each ws In Worksheets
lst = Sheets("Productivity").Range("B" & Rows.Count).End(xlUp).Row + 1
    
If IsEmpty(Sheets(ws.Name).Range("B2").Value) = True Then
            Sheets("Productivity").Range("B" & lst).Value = 0
End If
        
    Select Case ws.Name
        
        
        Case "Summary", "Productivity"
            'nothing
        Case Sheets(ws.Name).Range("B2").Value = "" Or Sheets(ws.Name).Range("B2").Value = 0
           Sheets("Productivity").Range("B" & lst).Value = 0
         
        Case IsEmpty(Sheets(ws.Name).Range("B2").Value) = False
            Sheets("Productivity").Range("B" & lst).Value = 0
                
        Case Else
                Sheets(ws.Name).Range("B2,D2").Copy
                With Sheets("Productivity")
                                .Range("B" & lst).PasteSpecial xlPasteColumnWidths
                .Range("B" & lst).PasteSpecial xlPasteValues
                End With
                
       End Select
Next
End Sub

 

[Fluff]

Try

Private Sub InboundProd()
   Dim ws As Worksheet
   Dim lst As Long
 
   For Each ws In Worksheets
      If ws.Name <> "Summary" And ws.Name <> "Productivity" Then
      lst = Sheets("Productivity").Range("B" & Rows.Count).End(xlUp).Row + 1
       
      If IsEmpty(ws.Range("B2").Value) Then
         Sheets("Productivity").Range("B" & lst).Value = 0
      Else
         ws.Range("B2,D2").Copy
         With Sheets("Productivity")
            .Range("B" & lst).PasteSpecial xlPasteColumnWidths
            .Range("B" & lst).PasteSpecial xlPasteValues
         End With
      End If
   Next ws
End Sub

 

[sbpersson]

Thanks for your quick response, however, this code resulted in a compile error = Next without For.

 

[Fluff]

oops missing an End If

Private Sub InboundProd()
   Dim ws As Worksheet
   Dim lst As Long
 
   For Each ws In Worksheets
      If ws.Name <> "Summary" And ws.Name <> "Productivity" Then
         lst = Sheets("Productivity").Range("B" & Rows.Count).End(xlUp).Row + 1
   
         If IsEmpty(ws.Range("B2").Value) Then
            Sheets("Productivity").Range("B" & lst).Value = 0
         Else
            ws.Range("B2,D2").Copy
            With Sheets("Productivity")
               .Range("B" & lst).PasteSpecial xlPasteColumnWidths
               .Range("B" & lst).PasteSpecial xlPasteValues
            End With
         End If
      [COLOR=#ff0000]End If[/COLOR]
   Next ws
End Sub

 

[sbpersson]

You are a life saver! Thanks so much.

 

[Fluff]

Glad to help & thanks for the feedback