data .range value not being copied across like the other are! Why?

[albertc30]

Hi all.

The code bellow was working fine with data 1 to 4 as variant, Invoices range A1 to D1 and Resize(1, 4)).

I since added another value go to another column and all still works but the value in Data4 is not being copied across.

This has now done my head in. I'll be checking this in the morning as I need my sleep now. It's 02:12 here in the UK and after a full on days work I am shattered.

Private Sub CBProfoInvoice_Click()

    Beep
    If MsgBox("You have selected a proforma invoice document." & vbNewLine & "This will generate your next invoice?", vbQuestion + vbYesNo, "Proforma to Invoice...") = vbNo Then

        Exit Sub
        
    End If

             'save invoice pdf code below
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
                "D:\Software\Invoices\INV" & Range("F4").Text & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                :=False, OpenAfterPublish:=False

            Dim Data(1 To 5) As Variant
            Dim DstRng As Range
            Dim RngEnd As Range
   
            Set DstRng = Worksheets("Invoices").Range("A1:E1")
            Set RngEnd = DstRng.Parent.Cells(Rows.Count, DstRng.Column).End(xlUp)
            Set DstRng = IIf(RngEnd.Row < DstRng.Row, DstRng, RngEnd.Offset(1, 0).Resize(1, 5))
       
            With Worksheets("Create")
                Data(1) = .Range("F4")  'Invoice number
                Data(2) = .Range("C12")  'Invoice total
                Data(3) = .Range("C14") 'Deposit
                Data(4) = .Range("C16") 'Owed
                Data(5) = .Range("F3") 'Proforma Number THIS IS NOT BEING COPIED ACROSS!!!
            End With
            
            DstRng = Data
               
               CBGenDocument.Enabled = False
               CBProfoInvoice.Enabled = False
                
               Sheets("Create").Select
                    
End Sub

As always, many thanks for all the time and help given. Cheers.

 

[albertc30]

Image attached...

Before invoice is generated from proforma, the proforma number is there.

 

[Fluff]

But what steps do I need to do, starting from the beginning.

 

[albertc30]

Video

On the proforma, once issued it will have the Proforma to Invoice button enabled and the Produce Invoice disabled. This because different code is to run on each one.

Once the Proforma to Invoice code runs, it then disables the Proforma to Invoice as way of saying the document has already been converted into an invoice.

The issue here is when the code runs on the Proforma to Invoice, it is not copying the data in cell F3 "Proforma Number" on to sheet Invoices column E so as that we see that X invoice was produced from a Proforma.

I have done my head about this and can't seem to find the reason as to why.

The code running is this one bellow;

Private Sub CBProfoInvoice_Click()

    Beep
    If MsgBox("You have selected a proforma invoice document." & vbNewLine & "This will generate your next invoice?", vbQuestion + vbYesNo, "Proforma to Invoice...") = vbNo Then

        Exit Sub
        
    End If

             'save invoice pdf code below
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
                "D:\Software\Invoices\INV" & Range("F4").Text & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                :=False, OpenAfterPublish:=False

            Dim Data(1 To 5) As Variant
            Dim DstRng As Range
            Dim RngEnd As Range
   
            Set DstRng = Worksheets("Invoices").Range("A1:E1")
            Set RngEnd = DstRng.Parent.Cells(Rows.Count, DstRng.Column).End(xlUp)
            Set DstRng = IIf(RngEnd.Row < DstRng.Row, DstRng, RngEnd.Offset(1, 0).Resize(1, 5))
       
            With Worksheets("Create")
                Data(1) = .Range("F4")  'Invoice number
                Data(2) = .Range("C12")  'Invoice total
                Data(3) = .Range("C14") 'Deposit
                Data(4) = .Range("C16") 'Owed
                Data(5) = .Range("F3") 'Proforma Number THIS IS NOT BEING COPIED ACROSS!!!
            End With
            
            DstRng = Data
               
               CBGenDocument.Enabled = False
               CBProfoInvoice.Enabled = False
                
               Sheets("Create").Select
                    
End Sub

Much appreciated for your time and patience in helping. Cheers.

 

[albertc30]

Thanks for the file, which sheet are you running the code from?
The only sheet that has the proforma button active is Sheet "3" but that is filling the Data array from the create sheet & F3 is blank on that sheet.

OMG!

I got you now!

Ops... The code is running on the wrong sheet and not from the proforma sheet i.e sheet 1, or 2 or 3.

Oh man!!! Gotta find a way to make that code run on the current sheet and not the Create.

At least I now know where the issue lies.

Thanks.

 

[albertc30]

With Worksheets("Create") 'This is my mistake. Need to run this from the select current sheet.
                Data(1) = .Range("F4")  'Invoice number
                Data(2) = .Range("C12")  'Invoice total
                Data(3) = .Range("C14") 'Deposit
                Data(4) = .Range("C16") 'Owed
                Data(5) = .Range("F3") 'Proforma Number THIS IS NOT BEING COPIED ACROSS!!!
            End With

 

[albertc30]

'With Worksheets("Create") 'This is my mistake. Need to run this from the select current sheet.
             With ActiveSheet
                Data(1) = .Range("F4")  'Invoice number
                Data(2) = .Range("C12")  'Invoice total
                Data(3) = .Range("C14") 'Deposit
                Data(4) = .Range("C16") 'Owed
                Data(5) = .Range("F3") 'Proforma Number THIS IS NOT BEING COPIED ACROSS!!!
            End With

You have pointed me in the right direction buddy.

Had I had the code to clear invoice data fields, this would have then reflected once saved into Invoices from Proforma which would then lead me to have seen the error of my ways.

This is why I do find coding intriguing and gets your brain thinking of ways to make it work or go around.

I might hide the proforma sheets, and will most definitely delete them once an invoice has been produced from it, or at least limit the time these are available for, thus reducing the size of the excel file in all.

I will also include another button to look for Proforma number (Whatever the sheet number is and bring it up) and/or say it no longer exists.

Much Much appreciated.

 

[Fluff]

Glad you sorted it & thanks for the feedback

 

[albertc30]

Glad you sorted it & thanks for the feedback

Not a problem.

Now dabbling on to the next piece of code;

Private Sub CBFindSheet_Click()

    Dim Answer$
        Answer = Application.InputBox("Enter new Sheet name")
    If Answer = "False" Then Exit Sub
    On Error GoTo 'Continue: 'Error here lol... lets google away.
    Sheets(Answer).Activate
    MsgBox "Sheet Exists"
    Exit Sub

End Sub

 

[Fluff]

As this is a totally different question, you will need to start a new thread.
Thanks

 

[albertc30]

As this is a totally different question, you will need to start a new thread.
Thanks

I shall mate. But first I'll give Google a good look into.

Again, many thanks for your help.