Not process the saving invoice & never go to the next unless complete specific cell

[DIMI]

Good evening
I have two problems with the following billing code: 1) I have configured it to store a copy on the hard drive but when I go and find the file I can edit it I do not want to be done. I still want to protect the excel sheet from changes But I can see it or save it as pdf, and 2) I would not let me save it and send me a message if it's not in the F16, E31, G31 cells.

Thank you.

Sub NextInvoice()
Range("I5").Value = Range("I5").Value + 1


Range("G26").Value = Range("G34")
Range("G30").Value = Range("G34")
Range("G31").MergeArea.ClearContents
Range("G34").MergeArea.ClearContents
Range("G38").MergeArea.ClearContents
Range("E31").MergeArea.ClearContents
Range("G34").Formula = "=G30-G31"

End Sub

Sub PostToRegister()
Dim Lrow As Long
Lrow = Sheets("list invoice").Cells(Rows.Count, 1).End(xlUp).Row
Dim inDate As Date, inNum As Long
inDate = Sheets("invoice").Cells(38, 7).Value
inNum = Sheets("invoice").Cells(5, 9).Value
Dim exDate, exNum As Long
exDate = Sheets("list invoice").Cells(Lrow, 1).Value
exNum = Sheets("list invoice").Cells(Lrow, 2).Value
If inDate >= exDate And inNum > exNum Then
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("invoice")
Set WS2 = Worksheets("list invoice")
'Figure out which row is the next row
NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1

'Write the important values to Register
'Write the important values to Register
WS2.Cells(NextRow, 1).Resize(1, 6).Value = Array(WS1.Range("G38"), WS1.Range("I5"), WS1.Range("H5"), _
WS1.Range("F16"), WS1.Range("G31"), WS1.Range("E31"))

Else
MsgBox "error."
End
End If
End Sub

Sub SaveInvWithNewName()

Dim NewFN
Dim variable1
Dim variable2

With ActiveSheet
variable1 = .Range("A32").Value
variable2 = .Range("A35").Value
.Copy
End With

With ActiveSheet
.Range("A32").Value = variable1
.Range("A35").Value = variable2
End With

With ActiveSheet
NewFN = "C:\invoice\" & Range("I5").Value & Range("H5").Value & Range("I49").Value & Range("F16").Value & ".xlsx"
ActiveSheet.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
ActiveWorkbook.PrintOut From:=1, To:=1, copies:=2
ActiveWorkbook.Close SaveChanges:=False
NextInvoice
End With
End Sub

Sub FINISH()
Call PostToRegister
Call SaveInvWithNewName
End Sub

[NoSparks]

Perhaps this...

Sub SaveInvWithNewName()

    Dim NewFN
    Dim variable1
    Dim variable2

With ActiveSheet    '<~~ this is the original invoice

    ' are these cells filled in?
    If .Range("F16") = "" Or .Range("E31") = "" Or .Range("G31") = "" Then
        MsgBox "some stuff missing"
        Exit Sub
    End If
    
    variable1 = .Range("A32").Value
    variable2 = .Range("A35").Value
    .Copy   'creates new active workbook containing this invoice
End With

With ActiveSheet    '<~~ now dealing with sheet in newly created workbook
    .Range("A32").Value = variable1
    .Range("A35").Value = variable2
    .Cells.Locked = True
    .Protect
    NewFN = "C:\invoice\" & .Range("I5").Value & .Range("H5").Value & .Range("I49").Value & .Range("F16").Value
End With

With ActiveWorkbook
    .SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook    '<~~ this will add the file extention .xlsx
    Application.DisplayAlerts = True
    .PrintOut From:=1, To:=1, copies:=2
    .Close SaveChanges:=False
End With
    
    Call NextInvoice

End Sub

[DIMI]

Originally Posted by NoSparks

Perhaps this...

Sub SaveInvWithNewName()

    Dim NewFN
    Dim variable1
    Dim variable2

With ActiveSheet    '<~~ this is the original invoice

    ' are these cells filled in?
    If .Range("F16") = "" Or .Range("E31") = "" Or .Range("G31") = "" Then
        MsgBox "some stuff missing"
        Exit Sub
    End If
    
    variable1 = .Range("A32").Value
    variable2 = .Range("A35").Value
    .Copy   'creates new active workbook containing this invoice
End With

With ActiveSheet    '<~~ now dealing with sheet in newly created workbook
    .Range("A32").Value = variable1
    .Range("A35").Value = variable2
    .Cells.Locked = True
    .Protect
    NewFN = "C:\invoice\" & .Range("I5").Value & .Range("H5").Value & .Range("I49").Value & .Range("F16").Value
End With

With ActiveWorkbook
    .SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook    '<~~ this will add the file extention .xlsx
    Application.DisplayAlerts = True
    .PrintOut From:=1, To:=1, copies:=2
    .Close SaveChanges:=False
End With
    
    Call NextInvoice

End Sub

Thank you for your help.
Because I want to make a first check on whether there are empty cells and send me a message and make me POSTTOREGISTER, I tried to adapt your own (I do not know if I put it at the right point) but it is a problem of drafting and finally I think that Locking the new spreadsheet does not succeed if there is any other way or is it better to save it as a pdf (I tried it but when I open it I get a message that the file is corrupted

Sub PostToRegister()
Dim Lrow As Long
Lrow = Sheets("LIST INVOICE").Cells(Rows.Count, 1).End(xlUp).Row
Dim inDate As Date, inNum As Long
inDate = Sheets("INVOICE").Cells(38, 7).Value
inNum = Sheets("INVOICE").Cells(5, 9).Value
Dim exDate, exNum As Long
exDate = Sheets("LIST INVOICE").Cells(Lrow, 1).Value
exNum = Sheets("LIST INVOICE").Cells(Lrow, 2).Value


If .Range("F16") = "" Or .Range("E31") = "" Or .Range("G31") = "" Or .Range("G38") = "" Then
MsgBox "some stuff missing"
Exit Sub

End If

If inDate >= exDate And inNum > exNum Then
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("INVOICE")
Set WS2 = Worksheets("LIST INVOICE")
'Figure out which row is the next row
NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1

'Write the important values to Register
'Write the important values to Register
WS2.Cells(NextRow, 1).Resize(1, 6).Value = Array(WS1.Range("G38"), WS1.Range("I5"), WS1.Range("H5"), _
WS1.Range("F16"), WS1.Range("G31"), WS1.Range("E31"))


Else
MsgBox "ERROR"
End
End If
End Sub

[NoSparks]

Well... I guess that should be looking at WS1 seeing those are cells being copied to WS2.

If you remove the dots from each .Range("somecell") it will refer to whatever sheet is active at the time, but that's not ideal.
Putting WS1 in front of the dots would do except that you haven't yet set the WS1 and WS2 variables.

You're best to declare all variables at the beginning of the sub.
Set the worksheets right away then use With statements to keep things together, reduce the typing and speed things up.

I'm not sure but maybe this is what your PostToRegister sub should be like.

Sub PostToRegister()
    Dim Lrow As Long
    Dim inDate As Date, inNum As Long
    Dim exDate, exNum As Long
    Dim NextRow As Long
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet

Set WS1 = Worksheets("INVOICE")
Set WS2 = Worksheets("LIST INVOICE")
   
With WS1
    If .Range("F16") = "" Or .Range("E31") = "" Or .Range("G31") = "" Or .Range("G38") = "" Then
        MsgBox "some stuff missing"
        Exit Sub
    End If
    ' populate variables
    inDate = .Cells(38, 7).Value
    inNum = .Cells(5, 9).Value
End With

With WS2
    ' populate variables
    Lrow = .Cells(Rows.Count, 1).End(xlUp).Row
    exDate = .Cells(Lrow, 1).Value
    exNum = .Cells(Lrow, 2).Value
End With

If inDate >= exDate And inNum > exNum Then
    With WS2
        'Figure out which row is the next row
        NextRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        'Write the important values to Register
        .Cells(NextRow, 1).Resize(1, 6).Value = Array(WS1.Range("G38"), WS1.Range("I5"), _
                WS1.Range("H5"), WS1.Range("F16"), WS1.Range("G31"), WS1.Range("E31"))
    End With
Else
    MsgBox "ERROR"
End If

End Sub

I'm not able to test things completely because I can only guess at what you're working with. If you're still having issues attach a sanitized workbook.

PS: please don't quote entire post and please use code tags.

[DIMI]

sorry i didn't know because is the first time i write to forum

We're so close when I'm running, but I do not get the data on the list invoices, but it's a new sheet with the price list and it saves it. That's what I want when I see that I have empty cells and when the number of the invoice and the date do not happen to get message ERROR and do nothing else.
Thank you

[NoSparks]

Attach a sample file so I have something to test against and can see what is actually happening.

[Pecoflyer]

@dimi
Could you please add code tags around your code? Click " go advanced" -" select code" and click the #button
Also do not quote entire posts unnecessarily. They make the thread hard to read - Thx