Excel macro to create files based on column

[Closed]
Report
-
Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
-
Hello,

I want to create a macro which creates files based on contents in column AJ. and I want the
macro to copy the data from original sheet to the resulting sheet (Only the relavant data for that location). I should have a set of files for each location with the file named as the location name.

I want the macro to create a location file even if there is no information in that sheet.

My excel file contains data from column A to column AJ. In column AJ the location names are available. Like Bangalore, Kochi, Delhi, Hydrabad, Mumbai, Kolkotha. I want the macro to create seprate file for each location (Even if the data is not available for that location). And save the files in the location name.

Thank you,
Praveen B J

1 reply

Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
487
Hi Praveen,

This macro will create files for each name found in column AJ, starting from row2 until the last data found. Files will be saved in the same folder as the file you are running the macro from.

Sub CreateFiles()
Dim lRow As Integer
lRow = Range("AJ" & Rows.Count).End(xlUp).Row
For Each cell In Range("AJ2:AJ" & lRow)
Workbooks.Add.SaveAs Filename:=cell.Value & ".xls"
Next cell
End Sub

Best regards,
Trowa