Macro to create files based on the contents Closed

Question

Hello,

I want to create a macro which creates files based on contents in column AJ. I want the macro to copy the data from original sheet to the resulting files (only the relevant data for that location). I should have the 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 for that location in the data sheet.

My excel file contains data from column A to column AJ. In column AJ the location names are available. Like Bangalore, Cochin, Delhi, Hyderabad, Mumbai, Kolkata. I want the macro to create separate file for each location ( Even if the data is not available for any of the location the macro should create a file) and save the files in the location name.

Thank you in advance for your help,

Praveen B J

TrowaD · Answer

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

TrowaD · Answer

Hi Praveen,

It's not clear to me what data you wish to copy along, so I just created the macro to get you started.

So please clearify which data needs to be carried over. Like same row as file name.
Also explain where you want that data to be put in the newly created file. Like first row or row 10 or something else.

Asking for path should be no problem, I'll start with it when the above is clear.

Best regards,
Trowa

bjpraveen · Answer

Hi Trowa,

I have written detailed steps of what I want.

I want to create a macro which helps me create 4 separate files based on the data in column AG in one tab. In column AG there are 3 kinds of data as show below.

1.	Approved
2.	Current Code issue
3.	Old Code issue

The macro should create the following 4 separate files as bellow.

1.	First file should include data related to the following data contained in column AG
a.	Approved
b.	Old Code Issue

Save the file with the file name as "Current Hours"

2.	Second file should include the data related to the following data contained in column AG
a.	Approved
b.	Current Code Issue

Save the file with the file name as "Approved Hours"

3.	Third file should include data related to the following data contained in column AG
a.	Current Code issue

Save the file with the file name as "Current Code issue"

4.	Fourth file should include data related to the following data contained in column AG
a.	Old Code Issue.

Save the file with the file name as "Old Code issue"

Also the macro should prompt for selecting the folder to save these 4 files.

Note: I want the macro to create all the four tabs in any case. (I.e even if any of the following values are not found in column AG, still the macro should create all the 4 files):

1.	Approved
2.	Current Code issue
3.	Old Code issue

I can provide example files, I don't know how to upload them here.

Thank you in advance for your help,

Praveen B J

Macro to create files based on the contents

3 responses

Similar discussions

Subscribe To Our Newsletter!