Copying password protected existing sheet to new workbook and renaming file as cell value

[Nilesh Parekh]

Dear Team,

I am working on creating an excel based calculator with formulas and macros. This file is heavy due to its data and the formulas (About 15 Mb).
I want a macro which could help me save the calculations for each customer as a new file with only values.
Since there are formulaes, the sheet & workbook is password protected where the password is '1234'.
The name of this file should be a cell value from the file say 'Q2'.
The name of the worksheet is 'Eligibility Sheet'
My script is able to create a new file with the sheet copies the text but fails to paste as it is password protected. Also the renaming of the file is not working.

I have written the below script but it fails as the file is pasword protected and the ren

Sub CopyToNew1()
'Copy the active sheet to a new Workbook.
  ActiveSheet.Copy
  ActiveWorkbook.SaveAs Filename:="Z:\New folder (2)\Book2.xlsx", _
  FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
  'Copy and PasteSpecial between workbooks
  Workbooks("Book2.xlsx").Worksheets("Eligibility Sheet").Range("A1:AF113").Copy
  Workbooks("Book2.xlsx").Worksheets("Eligibility Sheet").Range("A2:AF113").PasteSpecial Paste:=xlPasteFormats
  'Disable marching ants around copied range
  Application.CutCopyMode = False
  'Rename workbook as CIF / Card Number
  Dim rs As Workbook
  rs.Name = rs.Range("Q2")
  Next rs
 
End Sub

Can someone please help.

Thanks.