Copying value from cell not working...

[albertc30]

Hi all. Me again.

Another bit of code in dire need of help.

I have got the code bellow;

Private Sub CBGenDocument_Click()

    If Range("F3") = Empty Then 'Checks if next document value is empty.
        MsgBox "Please select your document type!", vbInformation, "Document..."
        Range("E3").Select
        Exit Sub
    End If
   
    If MsgBox("You have selected """ & Range("E3") & """ document." & vbNewLine & "Is this the document you wish to generate?", vbQuestion + vbYesNo, "Document type...") = vbNo Then
        Range("F3").Clear
        Range("E3").Select
        Exit Sub
    End If
   
    Select Case StrConv(Range("E3"), vbLowerCase)
        Case Is = "invoice"
            'Code here for 'invoice' option
            Sheets("Create").Range("F3").Copy
            Sheets("Invoices").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
            [COLOR=rgb(184, 49, 47)]Sheets("Create").Range("C12").Copy 'This cell contains formula "SUM(C6:C11)" which is the invoices total value. This copy function is actually copying the formula on this cell rather than the value in it!!! What am I doing wrong please?[/COLOR]
            Sheets("Invoices").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
           
                Range("F3").Select
                With Selection
                .HorizontalAlignment = xlCenter
                .Clear
                End With
                Range("E3").Select
        Case Is = "proforma"
            'Code here for 'proforma' option
            Sheets("Create").Range("F3").Copy
            Sheets("Proformas").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
           
                Range("F3").Select
                With Selection
                .HorizontalAlignment = xlCenter
                .Clear
                End With
                Range("E3").Select
      End Select
     
End Sub

Why is the code above in red not copying the value in that cell which is the result of the formula in it? The code is actually copying the cells' formula.

Any help truly appreciated. Cheers.

PS' Been looking at this code on the actual working sheet and might have to do something within these lines?

Dim Data(1 To 5) As Variant
    Dim DstRng As Range
    Dim RngEnd As Range
   
        Set DstRng = Worksheets("Saved Invoices").Range("A2:D2")
        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)) 'Changed the 4 to a 5... worked
       
        With Worksheets("Invoice")
            Data(1) = .Range("L17")  'Invoice number
            Data(2) = .Range("A17")  'Date
            Data(3) = .Range("AS1")  'Customer
            Data(4) = .Range("AZ73") 'Amount
            Data(5) = .Range("Z1")   'Not yet paid
        End With

 

[albertc30]

Hi.

I have tried this approach but it's not working. Perhaps I've missed something in the original code that I am trying to change.

Private Sub CBGenDocument_Click()

    If Range("F3") = Empty Then 'Checks if next document value is empty.
        MsgBox "Please select your document type!", vbInformation, "Document..."
        Range("E3").Select
        Exit Sub
    End If
    
    If MsgBox("You have selected """ & Range("E3") & """ document." & vbNewLine & "Is this the document you wish to generate?", vbQuestion + vbYesNo, "Document type...") = vbNo Then
        Range("F3").Clear
        Range("E3").Select
        Exit Sub
    End If
    
    Select Case StrConv(Range("E3"), vbLowerCase)
        Case Is = "invoice"
            'Code here for 'invoice' option
            'Sheets("Create").Range("F3").Copy
            'Sheets("Invoices").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
            'Sheets("Create").Range("C12").Copy 'This cell contains formula "SUM(C6:C11)" which is the invoices total value. This copy function is actually copying the formula on this cell rather than the value in it!!! What am I doing wrong please?
            'Sheets("Invoices").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
            
            
                'tried the below but no error but not copying any data accross either...
            Dim Data(1 To 2) As Variant
            Dim DstRng As Range
            Dim RngEnd As Range
   
            Set DstRng = Worksheets("Invoices").Range("A1:B1")
            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("F3")  'Invoice number
                Data(2) = .Range("C12")  'Invoice total
            End With
                'testing code above...

                Range("F3").Select
                With Selection
                .HorizontalAlignment = xlCenter
                .Clear
                End With
                Range("E3").Select
        Case Is = "proforma"
            'Code here for 'proforma' option
            Sheets("Create").Range("F3").Copy
            Sheets("Proformas").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
            
                Range("F3").Select
                With Selection
                .HorizontalAlignment = xlCenter
                .Clear
                End With
                Range("E3").Select
      End Select
      
End Sub

 

[albertc30]

Changed to this and it's working now.

     'tried the below but no error but not copying any data accross either...
            Dim Data(1 To 2) As Variant
            Dim DstRng As Range
            Dim RngEnd As Range
   
            Set DstRng = Worksheets("Invoices").Range("A1:B1")
            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("F3")  'Invoice number
                Data(2) = .Range("C12")  'Invoice total
            End With
            
            DstRng = Data 'Was missing this last bit in the code...