Copy of worsheet but told unable to

[ipbr21054]

I am using the code below.

The goal is to copy the existing worksheet, place it at the end of my other worsheets & take the name from cell G13
When i run it all works fine up until the code in Red below.
I then see this error supplied below message.

When i check the formula ACTIONLIST its actually on sheet called INFO
Do you see an issue as to why or can you advise another way to do the same task

Thanks

Private Sub Generate_Pdf_Click()
  Dim answer As Integer
  Dim sPath As String, strFileName As String
   Dim wks As Worksheet
  Set wks = ActiveSheet
  
  
  With ActiveSheet
  If Range("G13") = "" Then
    MsgBox "NO NAME SELECTED IN THE CUSTOMER DETAILS SECTION", vbCritical, "NO CUSTOMER SELECTED MESSAGE"
    Range("G13").Select 'CHECKING IF CUSTOMER IS SELECTED
  Exit Sub
  End If
  
  If Range("L18") = "" Then
    MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "PAYMENT TYPE WAS NOT SELECTED"
    Range("L18").Select 'CHECKING IF PAYMENT TYPE HAS BEEN SELECTED
  Exit Sub
  End If
  
  strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, fileName:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
  End With 'CURRENT INVOICE IS NOW SAVED
   
  With Sheets("DATABASE")
      Worksheets("DATABASE").Activate
  End With
    
  Set rng = ActiveSheet.Columns("A:A")
    findString = Worksheets("INV").Range("G13").Value
  Set cell = rng.Find(What:=findString, LookIn:=xlFormulas, _
    LookAt:=xlWhole, MatchCase:=False) ' CUSTOMER FOUND IN COLUMN A
    
  If cell Is Nothing Then
    MsgBox "NO CUSTOMER WAS FOUND"
  Else
  With Sheets("DATABASE")
    cell.Select
    ActiveCell.Offset(0, 15).Select ' CUSTOMERS CELL IN COLUMN P NOW SELECTED
  End With
  End If
    
  If Len(ActiveCell.Value) <> 0 Then
       ValueInInvoiceCell.Show 'MESSAGE SHOWN IF CUSTOMERS INVOICE CELL IN COLUMN P HAS A VALUE IN IT

  Exit Sub
  Else
       TransferInvoiceNumber.Show 'NOW ENTER INVOICE NUMBER IN CUSTOMERS CELL IN COLUMN P & NOW HYPERLINKED
  End If
    
  With Sheets("DATABASE")
      Worksheets("INV").Activate 'WORKSHEET INVOICE HAS NOW BEEN ACTIVATED
      End With
  With ActiveSheet
      'ActiveWindow.SelectedSheets.PrintOut copies:=1
  End With
  
  ' START OF COPY CODE HERE
  ActiveSheet.Copy After:=Worksheets(Sheets.Count) 'NEW WORKSHEET NOW CREATED
  If wks.Range("G13").Value <> "" Then
  On Error Resume Next
  ActiveSheet.NAME = wks.Range("G13").Value
  End If
  End With

  ' END OF COPY CODE
  wks.Activate
     Range("L4").Value = Range("L4").Value + 1 'INVOICE IS INCREMATED BY 1
     Range("G27:L36").ClearContents   'WORKSHEET DETAILS NOW CLEARED
     Range("G46:G50").ClearContents
     Range("L18").ClearContents
     Range("G13").ClearContents
     Range("G13").Select
     ActiveWorkbook.Save
  
  Call Sheet14.PasteIfFormulas_Click
  ActiveWorkbook.Save
  
  End With
  End Sub

 

[Micron]

Message seems clear enough. You have a named range on the sheet, the copied range contains/uses that name and you're trying to paste it on the same sheet. You can't duplicate the named range on the same sheet. IIRC, you can have the same Name on different sheets if the name is at the sheet level. If the named range is at the workbook level (i.e. not on any sheet) then AFAIK you cannot duplicate it at all in the workbook. If the issue is about a formula then I'm afraid I don't know anything about that but I'm fairly certain it's still about a named range.

If it's a named range issue, a solution might be to copy the range without pasting, then delete the name from the sheet then paste. AFAIK, the only way to do that would be to Set a range variable to your copied range, delete the Name and paste in the range you need. Unfortunately I can't recall doing that yet. I'm a newbie to Excel vba and am more familiar with Access vba. I suppose you could copy to the clipboard but that seems more difficult to me.

 

[ipbr21054]

I kind of understand what you are saying but theres to much for me to take on board.

Let me explain another way.
I need to make a copy of the active worksheet.
Cell G13 is the customers name.
The name is used to rename the copied worksheet.

So is there another way i can get the copied worksheet named ?
Cell G13 is a drop down list if that makes any difference

I thought the below might fix it using Worksheet Change event & changed the copy code to suit but still see the same message

Range("G2") = Range("G13").Value

 

[ipbr21054]

This would work but can you advise how to write it please.

Worksheet called DATABASE
Copy range A1:Z55 just written text etc NO formulas etc
Create new worksheet
Paste the range it just copied
Rename sheet.

Not sure how we reanme it unless the code take the name from the copied sheet at cell G13

 

[Micron]

I will do what I can later, but I've got some things I need to get done. At the moment, waiting a bit for lawn mower tractor battery to charge.

 

[ipbr21054]

I might have found the issue.
The name in the error message I use on another sheet.
I looked in name manager & found the name in question.
It said scope : workbook
This then might explain why I am unable to make the copy as it’s already in / on the workbook
Tomorrow I will start to delete all the ones that have workbook next to them as they should be assigned to my other worksheet & NOT all the workbook.

Will report back tomorrow.