Macro to create files based on the contents [Closed]

Report
Posts
3
Registration date
Wednesday June 27, 2012
Status
Member
Last seen
August 21, 2012
-
Posts
2761
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2021
-
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

3 replies

Posts
2761
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2021
462
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
Posts
3
Registration date
Wednesday June 27, 2012
Status
Member
Last seen
August 21, 2012

Hi Trowa,

Thank you for your reply,

For some reason the macro is not working as I wanted it to work. I think the macro is creating the files but it is not copying the relative data from the original sheet. Also is there a way to make this macro ask for the path to save the files.

Thank you once again for your help.

Praveen B J
Posts
2761
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2021
462
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
Posts
3
Registration date
Wednesday June 27, 2012
Status
Member
Last seen
August 21, 2012

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
Posts
2761
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2021
462
Hi Praveen,

Sorry for my late reply. A sample file will definitely help. Try www.speedyshare.com for that.
Curious how column AG is set up.

Don't be in a rush since I'm pretty busy.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!