Using VBA to copy and paste values into new workbooks but it's hardcoding original workbook at end of process

I have the below VBA Macro to go through a dropdown list of names and then copy and paste the values and formatting for each into a new workbook and then save based on the name. It works well except for the fact that at the end of the process it seems to be hardcoding all of the data in the original workbook as well.

Is there something I can change to prevent this from happening? Below is what I currently have.

Sub CreateWorkbooks()

Dim wb As Workbook
Dim ws As Worksheet
Dim nwb As Workbook
Dim nws As Worksheet
Dim rng As Range
Dim path As String
Dim myDate As String
Dim x As Integer
x = ActiveSheet.Range("C77").Value

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Summary")
Set rng = ws.Range("B2")
path = "X:\Private\Forecast\Forecasts by name\"
myDate = Format(Now(), "MM-DD-YYYY")

For i = 77 To x
rng = ws.Range("A" & i)

ws.Copy

Set nwb = ActiveWorkbook
Set nws = nwb.Worksheets("Summary")

With nws
Cells.Copy
Cells.PasteSpecial (xlPasteValues)
End With

Application.DisplayAlerts = False
nwb.SaveAs Filename:=path & rng & " " & myDate & ".xlsx", FileFormat:=xlWorkbookDefault
nwb.Close
Application.DisplayAlerts = True

Next i

End Sub

Solution

Copy Worksheet As Single-Worksheet Workbook

The Main Issue

At some point in the code, your source workbook becomes the active workbook overwriting the formulas with values. To prevent this from happening, you could check if the source workbook is the active workbook:

Set nwb = ActiveWorkbook
If nwb Is wb Then
    MsgBox "The source workbook became the active workbook on iteration " & i & "!", _
        vbExclamation
Else       
    Set nws = nwb.Worksheets("Summary")
    ' ... code between
    Application.DisplayAlerts = True
End If

This would also prove the following.

Sometimes there is an issue with Set nwb = ActiveWorkbook after copying a sheet to a single-sheet workbook. I don't know why, just following the 'reasonably-looking' recommendation:
```
Set nwb = Workbooks(Workbooks.Count)
```
referencing the workbook most recently added to the Workbooks Collection.

Other Issues

Use Option Explicit at the top of each module so you don't forget to declare all variables i.e. Dim i As Long.
Use the Long datatype for all integers unless required not to (e.g. e.g. Windows APIs).
Turn off screen updating to prevent screen flickering.
Use the smallest possible range to process. ws.Cells has many cells (1048576*16384), ws.UsedRange.Cells has mostly fewer.
Avoid Range.PasteSpecial whenever possible because it's slow. When copying values, the most efficient way is to copy values by assignment:
```
rgDest.Value = rgSource.Value
```
where rgSource and rgDest are two same-sized single-area ranges.
Use variables to avoid long (unreadable) lines e.g. nFilePath.

Option Explicit

Sub CreateSummariesByName()
    
    Const FOLDER_PATH As String = "X:\Private\Forecast\Forecasts by name\"
    Const SRC_FIRST_ROW As Long = 77
    Const DATE_FORMAT As String = "mm-dd-yyyy"
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    Dim sws As Worksheet: Set sws = wb.Sheets("Summary")
    Dim scell As Range: Set scell = sws.Range("B2")
    Dim sLastRow As Long: sLastRow = sws.Cells(SRC_FIRST_ROW, "C").Value
    
    Dim TodayString As String: TodayString = Format(Date, DATE_FORMAT)
    
    Application.ScreenUpdating = False
    
    ' Declare additional variables (to be used in the upcoming 'For...Loop').
    Dim nwb As Workbook, nws As Worksheet
    Dim n As Long
    Dim nName As String, nFilePath As String
    
    ' Loop...
    For n = SRC_FIRST_ROW To sLastRow
        
        nName = sws.Cells(n, "A").Value
        scell.Value = nName
        
        ' Create a copy of the source (summary) sheet in a new workbook.
        sws.Copy
        
        ' Reference this new workbook...
        Set nwb = Workbooks(Workbooks.Count)
        ' and the one and only sheet in it.
        Set nws = nwb.Sheets(1)
        
        ' Convert formulas to values (efficiently).
        With nws.UsedRange
            .Value = .Value
        End With
        
        ' Build the new file path (based on 'nName').
        nFilePath = FOLDER_PATH & nName & " " & TodayString & ".xlsx"
        
        ' Save and close the new workbook.
        Application.DisplayAlerts = False
            nwb.SaveAs Filename:=nFilePath, FileFormat:=xlWorkbookDefault
            nwb.Close SaveChanges:=False
        Application.DisplayAlerts = True
    
    Next n

    Application.ScreenUpdating = True
    
    ' Inform.
    MsgBox "Summaries by name created.", vbInformation

End Sub