Copy paste ignoring formula returning 0

[seang87]

Hi All,

I have a mock-up general journal template, this will copy and paste items from sheet1 to sheet2 and increment a unique reference. I’m happy with all the functionality up to there.

The issue:

column B generates a reference using a formula based on entries in column G. Until a value is entered into G, B will remain 0 or (containing formula =IF(G#>=1,E#,).
This leads to VB pasting the entire range even where B is 0 or (containing formula =IF(G#>=1,E#,) and I’m left with my entries, and then all rows in B there after filled with 0’s
I need it to ignore the row where B is 0 or (containing formula =IF(G#>=1,E#,)

Any help would be much appreciated!

Here is my code:

Sub click2()
Application.ScreenUpdating = False
  Dim myCheck
  Dim myCnt
  
  myCnt = Application.WorksheetFunction.CountA(Range("b7:b7"))
     'MsgBox myCnt
 
     If myCnt <> 1 Then
        MsgBox "YOU MUST ENTER A VALID REFERENCE."
        Exit Sub
     End If
 
    Dim LastRow1 As Long
    Dim LastRow As Long
    Dim LastCol As Long
    Dim sh As Worksheet
   
    Set sh = Sheets("Sheet1")
 
    Set sourceRng = ActiveSheet.Range("b7:n55")
 
    Sheets("Sheet1").Range("B7:N55").Copy
    Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
    Sheets("Sheet2").Range("S" & Rows.Count).End(xlUp)(2) = Now
   
    myCheck = MsgBox("WOULD YOU LIKE TO POST THIS JOURNAL?", vbYesNo)
    If myCheck = vbNo Then
        MsgBox "JOURNAL NOT POSTED."
        Application.CutCopyMode = False
        Exit Sub
    Else
        MsgBox "POSTED"
        Sheets("Sheet1").Range("c7:N54").ClearContents
        [A2].Activate
    End If
 
Application.CutCopyMode = False
Application.ScreenUpdating = True
 
    On Error Resume Next
    Set C = Range("Z1:AB1").Cells.SpecialCells(xlCellTypeConstants)
    For Each cc In C
        cc.Value = cc.Value + 1
    Next
 
End Sub