Macro closes wb (not supposed to)

[Jenawade]

I tried to use parts of two working macro's to make a new one for a repetitive task. When I run the macro, all it does is close the active workbook. What have I done wrong, What needs to change?

++++++++ I USED THIS ++++++++

Sub Make_Batch_Forms()
    Dim lngRowRef As Long, lngFileNr As Long
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    With Sheets("RFMD Form").Rows("4:11")
        For lngRowRef = 2 To 102
            lngFileNr = lngRowRef + 248
            ActiveWorkbook.SaveAs Filename:="2011-" & lngFileNr & ".xlsm", _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
           .Replace What:="$" & lngRowRef, Replacement:="$" & lngRowRef + 1, _
                LookAt:=xlPart
         Next lngRowRef
    End With
    ActiveWorkbook.Close SaveChanges:=False
    Application.DisplayAlerts = True
End Sub

++++++++ AND THIS ++++++++

Sub Example3_Loop()
'
' Example3_Loop
'
    StartVal = 1
    NumToFill = 12
    ActiveCell.Value = StartVal
    For Cnt = 0 To NumToFill - 1
        ActiveCell.Offset(Cnt, 0).Value = StartVal + Cnt
    Next Cnt
'
End Sub

++++++++ TO MAKE THIS ++++++++

Sub Make_Batch_Forms()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    With Sheets("PLQ Form").Rows("4:59")
    StartVal = 2
    EndVal = 26
    For Cnt = 0 To NumToFill - 1
            PQname = Range("R4").Value
            ActiveWorkbook.SaveAs Filename:=PQname & ".xlsm", _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
                
           .Replace What:="$" & StartVal, Replacement:="$" & StartVal + Cnt, _
                LookAt:=xlPart
                
         Next Cnt
    End With
    ActiveWorkbook.Close SaveChanges:=False
    Application.DisplayAlerts = True
End Sub

I just needs to save as the value of R4, then change the formulas to the next row down, ($2 to $3), then save as value of R4, chance $3 to $4, etc.

 

[VoG]

Does removing this line of code help?

    ActiveWorkbook.Close SaveChanges:=False

 

[Jenawade]

Why yes, removing that line does keep the file open. Go figure. But then it does nothing at all.

 

[VoG]

I'm not sure why it does nothing but you are missing a dot

PQname = .Range("R4").Value

 

[Jenawade]

Thank you!

 

[Jenawade]

It didn't make a difference but its good to have all the right bits in place.

 

[VoG]

You haven't defined what NumToFill is so it won't do anything

 

[Jenawade]

You haven't defined what NumToFill is so it won't do anything

I literally LOL'd at that one! Thank you! Its trying to work now. It errors at the save as, giving me an access error. Its trying to save in some weird location. How can I tell it to save at ThisWorkbook.Path?

NumToFill
[/code}

 

[VoG]

Try

ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & PQname & ".xlsm", _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

 

[Jenawade]

Its give me type mismatch error on that line.

Sub Make_Batch_Forms()


    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
    With Sheets("PLQ Form").Rows("4:59")
    StartVal = 2
    NumToFill = 26
    For Cnt = 0 To NumToFill - 1
    PQname = .Range("R4").Value
                            
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & PQname & ".xlsm", _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
                
         Next Cnt
    End With
    Application.DisplayAlerts = True
End Sub