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]

I meant to say data5 value is not being copied across not data4. sorry.

 

[albertc30]

compressed macro file

Hi everyone.

You can have a look at the file above.

I'm open to any suggestions.

Thank you.

 

[Fluff]

I cannot open that file, but from what I can on dropbox, F3 is merged with A3 & is therefore blank.

 

[albertc30]

TestTestFile

Not in at the moment.

But I'm totally confused with the F3 and E3 merged.

Might be right there in front of me but I just can't see it.

Cheers for your time.

I'll check this later again.

 

[Fluff]

I cannot open 7z files, so can you supply an uncompressed version.

 

[albertc30]

TestFile

 

[Fluff]

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.

 

[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.

That's because the invoice has already been generated from that proforma.

The issue being the data hasn't all been copied to invoices as in the Proforma number field.

 

[Fluff]

In that case can you please walk me through what steps I need to take, as the proforma buttons are disabled.