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

unklevig

New Member
Joined
Oct 10, 2014
Messages
12
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
wb.SaveAs sh1.Parent.Path & Application.PathSeparator & c.Value & ".xlsx"
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,111,703
Messages
5,541,303
Members
410,545
Latest member
Upsindustrial20
Top