Save As Macro failing, returning blank cell

[kitkatjam235]

I have a template that I need to create multiple files from, so I created a macro that will run through a column of state names and save the file as the particular state name in the state folder. It starts on cell (5, 35) and continues in a loop to (55, 35). It works for cell(5, 35) but returns a " " for the next state. My i shows it's looped to cell(6, 35) but both abrev and st are set for "". I'm not sure why it's not picking up the value of the state in the cell; I tried running the loop a few rows down and it ran for three states but then returned the "" again. Can someone explain why it would pick up a blank when the cell has a value?

Thanks for all help!


Sub StateFiles()
'
' StateFiles Macro
'
Dim i As Integer
Dim st As String
Dim abrev As String
For i = 5 To 55
abrev = Cells(i, 35)
st = Cells(i, 36)
Sheets("Triangles").Cells(i, 35).Copy
Sheets("Summary").Select
Range("K2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ChDir "W:\Filings\2015\Dentists\" & st
ActiveWorkbook.SaveAs Filename:= _
"W:\Filings\2015\Dentists\" & st & "\" & abrev & " Dentists Indication 2015.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


Next

End Sub

 

[Darren Bartrup]

Not 100% sure what's wrong, but Cells(i,36) and Cells(i,35) look at the currently active sheet.

After Sheets("Summary").Select the "Summary" sheet will be the active sheet and I can't see you setting it to the correct sheet before, so I'm guessing that on the second loop it's looking for the path names on the "Summary" sheet.

I'd use something like:

Sub StateFiles()


    Dim rPathRange As Range
    Dim rCell As Range
    
    Set rPathRange = ActiveWorkbook.Worksheets("Triangles").Range("AI1:AI35")
    
    'rcell - each cell in the range AI1:AI35
    'rcell.Offset(,1) - 1 cell to the right of rcell, so range AJ1:AJ35
    
    For Each rCell In rPathRange
        ActiveWorkbook.SaveAs "W:\Filings\2015\Dentists\" & rCell.Offset(, 1) & "\" & rCell.Value & _
            " Dentists Indication 2015.xlsm", _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Next rCell


End Sub

NB: This doesn't select and activate the sheet, just references it. Also, no need to change directory - just put the full path in the SaveAs. Also note - 'W' drive may not be the same from one computer to the next, so best to use the UNC path.