VBA Keeps Skipping a Paste

[jondavis1987]

So i have this section of vba.

    '   Open destination workbook and capture it as destination workbook
    Workbooks.Open "C:\Users\" & Environ("username") & "\Dropbox\Quality Control\Asphalt\Misc\Gradations Changes\" & destName & ".xlsm"
    Set scndDestWB = Workbooks(destName)
   
    Application.DisplayAlerts = False
    For Each rg In scndDestWB.Sheets(wsName).Range("A1:Z100")
        If rg = srcName Then GoTo Found
    Next rg
   
Found:
   
    srcWS.Range("H31:H44").Copy
    rg.Offset(1, 0).PasteSpecial xlPasteValues
   
    '   Save changes and close destination workbook
    scndDestWB.Close SaveChanges:=True

It's inside of a larger one posted below. It's supposed to find a matching name and then copy H31:H44 and paste it underneath the matching name. it pastes everything but for some reason it pastes nothing for H33. It doesn't skip it. The workbook it's pasting in had a value there before and when the new range pastes on top of it the cell is just blank and all the rest are filled in. The cell appears to be formatted the same as the other cells in both the source workbook and the destination workbook. There's parts of the larger vba where the same range is pasted into a different workbook and H33 pastes the correct way. What can I do to get H33 to paste into the one workbook.

Option Explicit

Sub Stockpiles()
   
    Dim srcWB        As Workbook
    Dim destWB       As Workbook
    Dim fName        As String
    Dim srcWS        As Worksheet
    Dim ws           As Worksheet
    Dim mstWS        As Worksheet
    Dim acWS         As Worksheet
    Dim destName     As String
    Dim wsName       As String
    Dim rg           As Range
    Dim srcName      As String
    Dim LocationName As String
    Dim scndDestWB   As Workbook
   
    Set srcWB = Workbooks("Stockpiles")
    Set srcWS = srcWB.Sheets("Stockpile Gradations")
    destName = srcWS.Range("D1").Text
    wsName = "Agg Gradations"
    srcName = srcWS.Range("C11")
    fName = Sheets("Stockpile Gradations").Range("C2").Value
   
    If srcWS.Range("C11").Value = "Crushed Asphalt" Then
        GoTo Crushed_Asphalt:
    End If
   
    '   Open destination workbook and capture it as destination workbook
    Workbooks.Open "C:\Users\" & Environ("username") & "\Dropbox\Quality Control\Aggregates\Stockpile Gradation\Stockpile Charts.xlsx"
    Set destWB = Workbooks("Stockpile Charts")
    Set ws = destWB.Sheets("Sheet1")
    Set mstWS = destWB.Sheets("Moistures")
    Set acWS = destWB.Sheets("AC")
   
    '   Unhide Sheet
    ws.Visible = True
    mstWS.Visible = True
    acWS.Visible = True
   
    '   Copy Sheet1 data from source workbook to destination workbook
    With ws
        .Range("A" & .Cells(Rows.Count, "A").End(xlUp).Row + 1).Resize(14, 1).Value = srcWS.Range("I11").Value
        .Range("D" & .Cells(Rows.Count, "D").End(xlUp).Row - 13).Resize(14, 1).Value = srcWS.Range("C9").Value
        .Range("E" & .Cells(Rows.Count, "E").End(xlUp).Row - 13).Resize(14, 1).Value = srcWS.Range("C11").Value
        .Range("F" & .Cells(Rows.Count, "F").End(xlUp).Row - 13).Resize(14).Value = srcWS.Range("A31:A44").Value
        .Range("G" & .Cells(Rows.Count, "G").End(xlUp).Row - 13).Resize(14).Value = srcWS.Range("J31:J44").Value
    End With
   
    '   Copy Moistures data from source workbook to destination workbook
    With mstWS
        .Range("A" & .Cells(Rows.Count, "A").End(xlUp).Row + 1).Value = srcWS.Range("I11").Value
        .Range("D" & .Cells(Rows.Count, "D").End(xlUp).Row + 0).Value = srcWS.Range("C9").Value
        .Range("E" & .Cells(Rows.Count, "E").End(xlUp).Row + 0).Value = srcWS.Range("C11").Value
        .Range("F" & .Cells(Rows.Count, "F").End(xlUp).Row + 0).Value = srcWS.Range("J18").Value
    End With
   
    '   Copy AC data from source workbook to destination workbook
   
    If srcWS.Range("I19").Value = "AC %" Then
        With acWS
            .Range("A" & .Cells(Rows.Count, "A").End(xlUp).Row + 1).Value = srcWS.Range("I11").Value
            .Range("D" & .Cells(Rows.Count, "D").End(xlUp).Row + 0).Value = srcWS.Range("C9").Value
            .Range("E" & .Cells(Rows.Count, "E").End(xlUp).Row + 0).Value = srcWS.Range("C11").Value
            .Range("F" & .Cells(Rows.Count, "F").End(xlUp).Row + 0).Value = srcWS.Range("J19").Value
        End With
       
       
    End If
   
    '   Hide Sheet
    ws.Visible = False
    mstWS.Visible = False
    acWS.Visible = False
   
    '   Save changes and close destination workbook
    destWB.Close SaveChanges:=True
   
    '   Open destination workbook and capture it as destination workbook
    Workbooks.Open "C:\Users\" & Environ("username") & "\Dropbox\Quality Control\Asphalt\Misc\Gradations Changes\" & destName & ".xlsm"
    Set scndDestWB = Workbooks(destName)
   
    Application.DisplayAlerts = False
    For Each rg In scndDestWB.Sheets(wsName).Range("A1:Z100")
        If rg = srcName Then GoTo Found
    Next rg
   
Found:
   
    srcWS.Range("H31:H44").Copy
    rg.Offset(1, 0).PasteSpecial xlPasteValues
   
    '   Save changes and close destination workbook
    scndDestWB.Close SaveChanges:=True
   
Crushed_Asphalt:
   
    '   Export source workbook to PDF
    With srcWB
       
        Sheets("Stockpile Gradations").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                          "C:\Users\" & Environ("username") & "\Dropbox\Quality Control\Aggregates\Stockpile Gradation\" & fName, Quality:=xlQualityStandard, _
                          includeDocProperties:=True, ignoreprintareas:=False, openafterpublish:=True
    End With
End Sub

 

[rlv01]

See if this makes any difference.

    '   Open destination workbook and capture it as destination workbook
        Workbooks.Open "C:\Users\" & Environ("username") & "\Dropbox\Quality Control\Asphalt\Misc\Gradations Changes\" & destName & ".xlsm"
        Set scndDestWB = Workbooks(destName)

    Application.DisplayAlerts = False
    For Each rg In scndDestWB.Sheets(wsName).Range("A1:Z100")
        If rg.Value = srcName Then GoTo Found
    Next rg

    MsgBox "'" & srcName & "' not found"
    scndDestWB.Close SaveChanges:=False
    Exit Sub
Found:

    With srcWS.Range("H31:H44")
        rg.Offset(1, 0).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

    '   Save changes and close destination workbook
    scndDestWB.Close SaveChanges:=True

 

[jondavis1987]

See if this makes any difference.

    '   Open destination workbook and capture it as destination workbook
        Workbooks.Open "C:\Users\" & Environ("username") & "\Dropbox\Quality Control\Asphalt\Misc\Gradations Changes\" & destName & ".xlsm"
        Set scndDestWB = Workbooks(destName)

    Application.DisplayAlerts = False
    For Each rg In scndDestWB.Sheets(wsName).Range("A1:Z100")
        If rg.Value = srcName Then GoTo Found
    Next rg

    MsgBox "'" & srcName & "' not found"
    scndDestWB.Close SaveChanges:=False
    Exit Sub
Found:

    With srcWS.Range("H31:H44")
        rg.Offset(1, 0).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

    '   Save changes and close destination workbook
    scndDestWB.Close SaveChanges:=True

I swapped it out and it still left an empty cell.

 

[MARK858]

What do both the below formulas return (change A2 to the cell reference H33 should have pasted in)?

=ISBLANK(A2)

=CODE(A2)

 

[jondavis1987]

What do both the below formulas return (change A2 to the cell reference H33 should have pasted in)?

=ISBLANK(A2)

=CODE(A2)

I just tried it out.

=ISBLANK(F4)

resulted in TRUE. The code one results in #VALUE!. I also just checked, because depending on the value of a cell it will paste in one of two workbooks. I changed something to see if it would paste in the other workbook and it also has the same thing where it's pasting everything except H33.

 

[jondavis1987]

I ran the code step by step so that when it pasted i could see it before it saves and closes and tried the code as well. The isblank is true and it just erases what was in the cell.

 

[rlv01]

I swapped it out and it still left an empty cell.

Consider adding some temporary debug code to analyze what is being copied and what is being pasted.

    'Debug code
    Dim A, B, C, D, E
    With srcWS.Range("H33")
            A = .Address(0, 0, , 1)
            B = .Value
            C = TypeName(.Value)
            D = Len(.Value)
            E = Asc(Left(.Value, 1))
    End With
    
    Dim V, W, X, Y, Z
    With rg.Offset(3, 0)
            V = .Address(0, 0, , 1)
            W = .Value
            X = TypeName(.Value)
            Y = Len(.Value)
            X = Asc(Left(.Value, 1))
    End With
    'End debug code

 

[rlv01]

Oops. That last bit should have been

Z = Asc(Left(.Value, 1))

 

[jondavis1987]

Oops. That last bit should have been

Z = Asc(Left(.Value, 1))

It gets to

 E = Asc(Left.Value, 1))

It says invalid procedure call or argument on the code

   'Debug code
    Dim A, B, C, D, E
    With srcWS.Range("H33")
            A = .Address(0, 0, , 1)
            B = .Value
            C = TypeName(.Value)
            D = Len(.Value)
            E = Asc(Left(.Value, 1))

 

[jondavis1987]

I'm not sure exactly what happened. But I put the code back to where it was without the debug. I just went through and retyped the formula in the cell that generates a value in H33. Retried it and it worked that time. I don't know if that's like some equivalent of turning it off and back on, but the formula was the exact same thing. It is working now though.