Save Range Sheet 1 as filename for closed excel worksheet

[rpunger1957]

I am new to macros and I want to do something I am sure someone else would like to do.


I have two spreadsheets.

Spreadsheet 1 is closed and perfected to the way I need it.
Second Spreadsheet 2 is a spreadsheet of 60 names that I want to rename with Spreadsheet 1 Names.
Below is the vba I am working with but isn't doing what I want.

Thank you to anyone who can help me please.


Sub Myfilename()


While A < 60

Dim Path As String
Dim filename As String
Path = "C:\TimeCard"


Application.Wait (Now + TimeValue("00:00:03"))
Set WshShell = CreateObject("wscript.Shell")


Windows("TestThis.xls").Activate
filename = Range("A1")


Workbooks.Open ThisWorkbook.Path & "" & "2019 AFRC_Timesheet v54.xls"
ActiveWindow.WindowState = xlMinimized
ThisWorkbook.Activate


Application.Wait (Now + TimeValue("00:00:03"))
Set WshShell = CreateObject("wscript.Shell")


ActiveWorkbook.SaveAs filename:=Path & filename & " 2019 AFRC_Timesheet v54.xlsm", FileFormat:=xlNormal
ActiveWorkbook.Close SaveChanges:=True

Application.Wait (Now + TimeValue("00:00:03"))
Set WshShell = CreateObject("wscript.Shell")


Windows("TestThis.xls").Activate

Wend


A = A + 1

End Sub