Create multiple folders and save spreadsheet into created folder

jon78981

New Member
Joined
Jun 26, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Hello,

test macro create folder - Copy.xlsm
ABCDEFGHIJK
15555folder and spreadsheet number2Number of folders to make
21A\1A and the backward slash in adajacent cell to help with path to folder location creation
3
4
5
6
7
8C:\test\path location helper
9
10C:\test\1A\path location helper
111A5555folder and spreadsheet number helper
Sheet1
Cell Formulas
RangeFormula
A10A10=CONCATENATE(A8,A2,B2)
A11A11=CONCATENATE(A2,A1)





I am trying to write a macro.
Purpose of macro is to create a specified number of named folders into a particular file path location. The number of folders to create is at cell D1
The folders name is tp be from mutiple cell values in the spreadsheet. The folders name would be 1A as per cell A2, and then add on the number from cell A1 to that.
to give a folder name of 1A555
Into each created folder the spreadsheet that the macro was run from would be saved into that newly created folder.
The spreadsheet would be named from multiple cell values in the spreadsheet. It would be named from parts of the file path and folder name.
The spreadsheet would be named 1A555. taking the value entered into cell A1 and A2.

The spreadsheet saved into the folder must retain the the cell value as specified in cell A1 when the macro button was clicked to create the folder.

So in real life example when the macro was run using the example above.

The folder C:\test\1A already exists on my drive.

A folder would created in C:\test\1A\1A555. The folder would be named 1A555.
The spreadsheet would be saved into that folder. The spreadsheet would be named 1A555. The cell at A1 would still state 555. The details shown above in the table example would be in the spreadsheet.


Then the macro would run a second time, a Loop i think. As the number 2 is stated in cell D1. This time cell A1 would state 556.
A folder would be created in C:\test\1A\1A556. The folder would be named 1A1556. The spreadsheet would be saved into that folder. The spreadsheet would be named 1A556. The cell at A1 would state 556

so each loop +1 is added to the value the value at A1. and that drives how the folder is named. and spreadsheet named.


I would need to keep looping through the code the specified number of times as stated in D1.( the number of folders to create)


I think there is a Do Until Loop or some type of loop involved to get this to work. and maybe making a Interger.

I have got code below, which works to create an individual folder and save the spreadsheet. But i cannot bring the D1 cell into play to make the number of required folders. And i havent managed yet to figure out how to write the loop to make the +1 style increment each loop until 2 is reached.


Sub Macro1()
Dim strFilename, strDirname, strDirnametwo, strPathname, strDefpath As String

strDirname = Range("A11").Value ' New directory name
strDirnametwo = Range("A3").Value ' 2nd layer of directory folder name


strFilename = Range("A11").Value 'New file name
strDefpath = Range("A10").Value 'Default path name


If Dir(strDefpath & strDirname & strDirnametwo, vbDirectory) = "" Then
MkDir strDefpath & strDirname & strDirnametwo
End If

strPathname = strDefpath & strDirnametwo & strDirname & "\" & strFilename 'create total string
ActiveWorkbook.SaveAs Filename:=strPathname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Watch MrExcel Video

Forum statistics

Threads
1,127,822
Messages
5,627,096
Members
416,220
Latest member
Sykotik7S

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