MkDir Question - saving workbook within newly created directory?

TheWaterDog

New Member
Joined
May 24, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
This is so painfully close to working - it needs a tiny tweek and I'm hoping a genius can help with the last little bit?

I've got an index [named A3 Materials Requisition] that uses a Command Button to add the next number to an existing sequence [named IE79MR001, IE79MR002, etc].
It logs the date and time the user does this.
It creates a new file [named IE79MR001, IE79MR002, etc]
It opens a template [named A3 MR Template]
It renames the template as the new number to the existing sequence [IE79MR001, IE79MR002, etc]

So....that all works perfectly...but I'm having an issue with actually saving the renamed document in the new directory because I don't know how to 'ask' for the path (because it's dependent on the last number of the sequence).

At the moment I have it saving in the same place as the new directory, not in it - can anyone please be kind enough to point me in the right direction of how I save it within the new directory that it's just made? (Please forgive the lunatic amount of notes I put into my code - literally everything in my brain falls out overnight and so I honestly have no idea what I was doing the day before if I don't graffiti it)


VBA Code:
Private Sub CommandButton1_Click()
'______________________________________________________________________________________
'Lucy Notes - September 2022
'No1 - Log the creation of the MR by entering data into the A3 Index
'______________________________________________________________________________________

'Find next available blank and continue sequence of MR numbers
If Application.WorksheetFunction.CountA("A:A") = 0 Then
[a1].Select
Else
On Error Resume Next
Columns(1).SpecialCells(xlCellTypeBlanks)(1, 1).Select
If Err <> 0 Then
On Error GoTo 0
[A65536].End(xlUp)(2, 1).Select
End If
On Error GoTo 0
End If

'Add todays' date and time into the next cell
With Range("A" & Rows.Count).End(xlUp).Offset(1)
    .Value = .Offset(-1).Value + 1
    .Offset(, 1).Value = Now
End With

'______________________________________________________________________________________
'Lucy Notes - September 2022
'No2 - Create the new MR by opening the template, renaming it as the last logged sequence and creating a new directory with the same name
'______________________________________________________________________________________

'Create the New MR Directory
MkDir "C:\Users\Me\Desktop\A3 Materials Requisition\A3 Raised MR\" & "IE79 MR00" & [A65536].End(xlUp)

'Open the MR template
    Workbooks.Open "C:\Users\Me\Desktop\A3 Materials Requisition\A3 Templates\A3 MR Template.xlsm"
    Dim Path As String
    Dim filename As String

'Save MR as the new number entered in the sequence
Path = "C:\Users\Me\Desktop\A3 Materials Requisition\A3 Raised MR\"
filename = "IE79 MR00" & [A65536].End(xlUp)
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsm", FileFormat:=52

'______________________________________________________________________________________
'Lucy Notes - September 2022
'No3 - Close and save the index leaving the new document open for the user
'______________________________________________________________________________________

'Save and close the A3 Index
Workbooks("A3 Materials Requisition.xlsm").Close SaveChanges:=True
filename = "IE79 MR00" & [A65536].End(xlUp)
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsm", FileFormat:=52

End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,889
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Path = "C:\Users\Me\Desktop\A3 Materials Requisition\A3 Raised MR\IE79 MR00" & [A65536].End(xlUp) & "\"
 
Solution

TheWaterDog

New Member
Joined
May 24, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Path = "C:\Users\Me\Desktop\A3 Materials Requisition\A3 Raised MR\IE79 MR00" & [A65536].End(xlUp) & "\"

FLUFF!!!!!!
Marry me immediately!!!!

Works like an absolute dream - you are, as always, my hero!!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,889
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
1,497
Office Version
  1. 365
Platform
  1. Windows
...and when is the wedding? 👰:biggrin:
 

Forum statistics

Threads
1,181,974
Messages
5,933,031
Members
436,872
Latest member
Nickytoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top