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]

Exactly what is in R4?

 

[Jenawade]

Exactly what is in R4?

A formula pulling in a string of numbers and letters from another (open) workbook.

 

[VoG]

If that string contains an illegal character like / then you'll get an error.

 

[Jenawade]

The cell contains: ='C:\Users\TJA\Desktop\[PQs.xlsm]Sheet1'!$A$2

And the value is: 211168-19S PQ05654C

So what if I have it copy/paste values, save as (R4 value), close that file, reopen the original with formula's, replace $2 in my formulasto $3 (next line down in my data sheet), copy/paste values, save as R4, etc?

 

[VoG]

Sorry, I can't see why that is failing.

 

[RoryA]

What is the current code you are trying, based on Peter's changes so far? I suspect your issue is that the replace code is looking for a row number that you have already changed to something else, so it replaces nothing and you end up trying to save a file using a name that already exists. I think SaveCopyAs might be a better bet here, unless you are altering the workbook format (eg from xls to xlsm)

 

[Jenawade]

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
                
           .Replace What:="$" & StartVal, Replacement:="$" & StartVal + Cnt, _
                LookAt:=xlPart

         Next Cnt
    End With
    Application.DisplayAlerts = True
End Sub

It gives an access error for a folder that doesn't exist. When I just ran it a moment ago, it wanted this path plus \N\C31D0000, so I thought, I'll just create that folder! Ran it again, then it wanted this path plus \N\N\9EBD0000.

 

[RoryA]

If your name in R4 includes backslashes they will be treated as folder names. Since you will have saved the previous file into a new folder, the Activeworkbook.Path will already include that new folder and you will end up need another subfolder and so on.

I'm guessing you want something like:

Sub Make_Batch_Forms()
   Dim wbk As Workbook
   Dim strPath As String

   Application.ScreenUpdating = False
   Application.DisplayAlerts = False

   Set wbk = ActiveWorkbook
   strPath = wbk.Path & "\"
   
   With wbk.Sheets("PLQ Form")
      startval = 2
      NumToFill = 26
      For cnt = 1 To NumToFill
         PQname = .Range("R4").Value

         .Rows("4:59").Replace What:="$" & startval, Replacement:="$" & startval + 1, LookAt:=xlPart
         wbk.SaveCopyAs FileName:=strPath & PQname & ".xlsm"
         startval = startval + cnt

      Next cnt
   End With
   Application.DisplayAlerts = True
   wbk.Close False
End Sub

 

[Jenawade]

I've tried to add the copy/paste values and R4 contains "211168-19S PQ05654C" but PQname = .Range("R4").Value contains "N/A" for some reason.

 

[Jenawade]

I've changed

strPath & PQname & ".xlsm"

To

strPath & cnt & ".xlsm"

And it works great now; I just need to rename the files manually. That's much easier than creating each one manually! Thanks for all the help!