Copy paste ignoring formula returning 0

seang87

New Member
Joined
Oct 22, 2014
Messages
11
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:

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

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Forum statistics

Threads
1,143,656
Messages
5,720,116
Members
422,266
Latest member
Mattyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top