Create multiple folders and save spreadsheet into created folder

[jon78981]

Hello,

test macro create folder - Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	5555	folder and spreadsheet number		2	Number of folders to make
2	1A	\	1A and the backward slash in adajacent cell to help with path to folder location creation
3
4
5
6
7
8	C:\test\	path location helper
9
10	C:\test\1A\	path location helper
11	1A5555	folder and spreadsheet number helper
Sheet1

Cell Formulas
Range	Formula
A10	A10	=CONCATENATE(A8,A2,B2)
A11	A11	=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