How do I save down numerous files from one list with a new name from a different list?

Question

I'm attempting to create a macro that opens up a file specified within a range(Workbooks), refreshes it and then saves it down as a new file with a name which is specified within another range(SavedNames) and then moves on to the next cell in the range(Workbooks), refreshes it and saves that down with the name of the next cell in the second range(SavedNames) and so on. I thought I could use the 'For each cell' method again for the SavedNames range however it comes up with the error message 'For control variable already in use'

Public Sub Refresh_All()

Dim filepathstr As String
Dim filename As String
Dim wbk As Workbook
Dim name As String

filepathstr = Sheet1.Range("filepath").Value

For Each cell In Sheet1.Range("workbooks")

If Not cell.Value = "" Then

    filename = cell.Value

    Set wbk = Workbooks.Open(filepathstr & filename, False)


    ''''**REFRESH**''''''
    SAPBexrefresh (True)

    Application.DisplayAlerts = False

        For Each cell In Sheet1.Range("SavedNames")
        If Not cell.Value = "" Then
        name = cell.Value

        wbk.SaveAs (name)
        wbk.Close False
        End If
        Next cell

    Application.DisplayAlerts = True

End If

Next cell

MsgBox "The Macro has finished; BW Reports are refreshed"


End Sub

If anyone has a solution or work around it would be greatly appreciated!

Answer 1

For your exact question - how to use a For loop inside another For loop - you'd need to use a different variable for the second loop. Something like:

Dim rngOpen As Range
Dim rngSave As Range

For Each rngOpen In Sheet1.Range("workbooks")
    ...

    For Each rngSave In Sheet1.Range(SavedNames)
        ...
    Next rngSave
Next rngOpen

But...

This seems like a pretty cumbersome way of doing things. Isn't there a relationship between the cell that stores the "Open" names and the "Save" names? Are they in two columns next to one another or similar? Something like this:

Open | Save
Wbk1 | New1
Wbk2 | New2
Wbk3 | New3

If so, you can use cell.offset(0,1) to refer to the cell in the next column over to have an easier time of it.

Dim rngOpen As Range
Dim rngSave As Range

For Each rngOpen In Sheet1.Range("workbooks")
    ...
    wbk.SaveAs filename:=rngOpen.offset(0,1).value        
Next rngOpen

Doing a second loop is counterproductive, and it's only ever going to find you the first entry in the list; you'd have to delete your save names as you go for it to work otherwise.