VBA Case Not Recognizing Blank cells

Status
Not open for further replies.

sbpersson

New Member
Joined
May 8, 2018
Messages
8
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!



Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,109,070
Messages
5,526,609
Members
409,712
Latest member
lager2020

This Week's Hot Topics

Top