Unusual behavior now code is renaming Sheet Data to Proforma number and saving actual proforma as Create(1)...

[albertc30]

Hi all.

Once I start I can't stop. Maybe this is my biggest issue.

trying now to implement all the code together, and not having changed nothing to the code bellow, I don't think, now it's behaving totally different.

It used to save the document if Proforma was selected as a new sheet with the name being the Proforma number.

Now, it's changing my Data sheets name from Data to Proforma number and duplicating the Create sheet as Create (1) and so on.

What on earth did I do now?

Private Sub CBGenDocument_Click()

        'ActiveSheet.Unprotect Password:="test"

       If Range("F3") = Empty Then 'Checks if next document value is empty.
        'Beep
        MsgBox "Please select your document type!", vbInformation, "Document..."
        Range("E3").Select
        Selection.ClearContents
        ActiveWorkbook.save
        Exit Sub
    End If
    
    Beep
    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
        Selection.ClearContents
        ActiveWorkbook.save
        Exit Sub
    End If
    
    Select Case StrConv(Range("E3"), vbLowerCase)
        Case Is = "invoice"
            'Sheets("Invoices").Unprotect "test"
            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("F3")  'Invoice number
                Data(2) = .Range("C12")  'Invoice total
                Data(3) = .Range("C14") 'Deposit
                Data(4) = .Range("C16") 'Owed
                Data(5) = .Range("P2") 'N/A
            End With
            
            DstRng = Data
            
            'Sheets("Invoices").Protect "test"

'save invoice pdf code below
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
                "D:\Software\Invoices\INV" & Range("F3").Text & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                :=False, OpenAfterPublish:=False
        
'bellow clears invoice number after saved in pdf
                Range("F3").Select
                With Selection
                .HorizontalAlignment = xlCenter
                .Clear
                End With
                Range("E3").Select
                Selection.ClearContents
                ActiveWorkbook.save
                
'Proforma code bellow...
            Case Is = "proforma"
            
                'Sheets("Proformas").Unprotect "test"
            
                Set DstRng = Worksheets("Proformas").Range("A1:D1")
                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, 4))
            With Worksheets("Create")
                Data(1) = .Range("F3")  'Proforma number
                Data(2) = .Range("C12")  'Proforma total
                Data(3) = .Range("C14") 'Deposit
                Data(4) = .Range("C16") 'Owed
            End With
            
                DstRng = Data
                
                'Sheets("Proformas").Protect "test"
            
'save proforma pdf code below
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
                "D:\Software\Proformas\PROF" & Range("F3").Text & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                :=False, OpenAfterPublish:=False
        
                    Range("F4").Select 'Show number in white
                    With Selection.Font
                        .ColorIndex = xlAutomatic
                        .TintAndShade = 0
                    End With 'Show number in white
                    
                    Range("E4").Select 'Show number in white
                    With Selection.Font
                        .ColorIndex = xlAutomatic
                        .TintAndShade = 0
                    End With 'Show number in white
                    
                    CBGenDocument.Enabled = False
                    CBProfoInvoice.Enabled = True
               
                Sheets("Create").Copy After:=Sheets(Sheets.Count) 'save as new sheet
                On Error Resume Next
                ActiveSheet.Name = Range("F3").Value
                On Error GoTo 0
                
                'testing here
                'Dim ws As Worksheet

                'For Each ws In ThisWorkbook.Worksheets
                'If ws.Name = "Create" Or ws.Name = "Products" Or ws.Name = "Customers" Or ws.Name = "Invoices" Or ws.Name = "Proformas" Or ws.Name = "Data" Then

                 '   ws.Visible = True
                'Else
                 '   ws.Visible = False
                'End If
                'Next ws
                'testing the aboce
                
                CBGenDocument.Enabled = True
                CBProfoInvoice.Enabled = False
                              
                'ActiveSheet.Protect Password:="test"
                    
                    Sheets("Create").Select
                    
                    Range("F4").Select  'Hide number in white
                        With Selection.Font
                            .ThemeColor = xlThemeColorDark1
                            .TintAndShade = 0
                        End With        'Hide number in white
                        
                        Range("E4").Select  'Hide number in white
                        With Selection.Font
                            .ThemeColor = xlThemeColorDark1
                            .TintAndShade = 0
                        End With        'Hide number in white
                   
                Range("F3").Select
                With Selection
                .HorizontalAlignment = xlCenter
                .Clear
                End With
                Range("E3").Select
                Selection.ClearContents
                ActiveWorkbook.save
      End Select
      
End Sub

As always, much appreciated any help and any input.
Cheers.

FileHere

 

[albertc30]

Gone back to a previous saved file. Managed to produce 5 proformas, then successfully converted them to invoices.

Then going back to producing proformas, it now changed the sheet Data name, only the name to 6 which was the next Proforma number and the actual proforma was saved as Create (2)!!!

Bonkers!!!!

 

[albertc30]

Done a bit more digging and I've hidden sheet Data and now the code has taken the LAST sheet which was Proformas and changed it's name to the number of the proforma, and off course, it also took Create sheet and duplicated into Create (2)!

I'm guessing the issue lies on the bit of code bellow?

Sheets("Create").Copy After:=Sheets(Sheets.Count) 'save as new sheet with the proforma number as name for sheet
                On Error Resume Next
                ActiveSheet.Name = Range("F3").Value 'Proforma number for new sheet name
                    'Me.Visible = xlHidden 'Testing this
                On Error GoTo 0

 

[albertc30]

StableVersionHere

Now I'll just have to pay attention to what is it that I am changing/adding to the code that's causing it.

Step by Step.

Tomorrow is another day!

Good night everyone and many thanks.