Save newly created workbooks to current folder using macro in personal workbook

[unklevig]

Hi Everyone,

I'm running into a problem with the following code when I place it into my "Personal.xlsb" (it works perfect when ran from a module in the "This Workbook" module). When I run the code from my personal workbook it is saving the newly created workbooks to where the "Personal.xlsb” is located, so I tried changing it as follows with the intent of saving the new workbooks to the file location of the workbook I'm running the macro on:

Any help on making the macro work from my "Personal" workbook would be greatly appreciated.

Sub create ()

'From JLGWhiz on MrExcel

Dim wb As Workbook, sh1 As Worksheet, lr As Long, rng As Range
Set sh1 = Sheets("CAB")
Set sh2 = Sheets ("CAV")
lr = sh1.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = sh1.Range("A5:A" & lr)
  For Each C In rng
    Sheets("CAL").Copy
Set wb = ActiveWorkbook
wb.Sheets(1).Range("A1") = C.Value
sh2.Copy After:=wb.Sheets(1)

[COLOR=rgb(226, 80, 65)]‘FROM:[/COLOR]

‘wb.SaveAs ThisWorkbook.Path & Application.pathseparator & C.Value & ".xlsx" 'This saves to .xlsb workbook folder location

[COLOR=rgb(226, 80, 65)]‘TO:[/COLOR]

wb.SaveAs ActiveWorkbook.Path & Application.pathseparator & C.Value & ".xlsx" [COLOR=rgb(226, 80, 65)]'This is where the following error occurs:

‘Run-time error of "1004: Microsoft Excel cannot access the file 'C:xxxxxx. There are several possible reasons:

‘The file name or path does not exist

‘The file is being used by another program

‘The workbook you are trying to save has the same name as a currently open workbook."[/COLOR]

wb.Close False
Next
End Sub

 

[Fluff]

Try

wb.SaveAs sh1.Parent.Path & Application.PathSeparator & c.Value & ".xlsx"

 

[unklevig]

Try

wb.SaveAs sh1.Parent.Path & Application.PathSeparator & c.Value & ".xlsx"

Thank you so much! I really appreciate your time. This worked perfectly!

 

[Fluff]

You're welcome & thanks for the feedback.