PDA

View Full Version : [SOLVED] Stop macro running if row empty



psthink
12-22-2020, 03:49 AM
Hi,

This is my first ever post on any forum ever so firstly I apologise if I don't follow the correct protocol! I have little knowledge of VBA but learning via experience (and youtube) rather than a course

I've got a Macro that copies a row and paste's the data to another workbook where it's stored, is it possible to prevent the macro from being run if any of the cells in row 3 are empty? (Row 3 - B3 to K3)

This is my macro to "Submit" the data:

Sub submit()
'
' submit Macro
'

'
Rows("3:3").Select
Selection.Copy
Workbooks.Open Filename:="N:\REPORTING\Liam\MTA Log.xlsm"
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("B3").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
Selection.ClearContents
Range("B3").Select
End Sub


Thank you in advance,
PS

p45cal
12-22-2020, 06:26 AM
maybe:
Sub submit2()
If Application.CountBlank(Range("B3:K3")) = 0 Then
With Rows(3)
.Copy
Workbooks.Open Filename:="N:\REPORTING\Liam\MTA Log.xlsm"
Rows(3).Insert
ActiveWorkbook.Close True
Application.CutCopyMode = False
.ClearContents
End With
Range("B3").Select
Else
MsgBox "blanks in B3:K3"
End If
End Sub

psthink
12-22-2020, 06:56 AM
Hi P45cal,

Thank you so much! That works perfectly, appreciate your help