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:
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: