A macro to create a new workbook and copy data

Hello Excel Forum members,

I was wondering if anyone could assist me with my issue. I'm trying to write a macro that would copy the data based on the value in U1 cell (All Cases tab), paste it into a new workbook and save that workbook based on the value in W1 cell. So say if I input 13 in U1 the macro would copy the data from A1 to R5, paste it into a new workbook and save it under ID 13 name. The new workbook would look like ID 13 tab.

Thank you for your help!

Hi Alf,

Thank you so much for your help. The macro is working. I also have a follow-up question- what if I create a second tab (All Ids) where I'd store every single ID in the population (13,45,55,101 and s on and so forth)- would that be possible to create a macro that just runs those IDs one by one in a single command and saves them in the corresponding directory ?

Thank you!

would that be possible to create a macro that just runs those IDs one by one in a single command and saves them in the corresponding directory ?

Yes that should be no problem. What I would suggest is a macro that starts by copying all values from the A column to an new sheet then extracts the unique values to a list. Macro then loops through this list and setting an autofilter based on the values. Then adds a new sheet with the filtered values and save that as a new file named "ID " + cell value. Delets this sheet and goes to the next value.

Finally sheet with unique values are deleted and autofilter removed from sheet "All cases". As mention earler you need to change the fPath strin to a folder of your choice.

Please Login or Register  to view this content.

Alf

Alf,

Thanks again for the suggestion. The macro saves all the data of each unique ID number as a separate files. However, for some reason only the first ID on the list (ID 13) gets fully copied over. The rest of the ID's have their corresponding files created but without any information. In addition, when I first ran the macro it gave me an error on this line:

ActiveSheet.Name = "All IDs"

I changed it to ActiveSheet.Name = "Sheet1" and it seemed to work except for the issue described above.

Thank you again for your help!

error on this line:

ActiveSheet.Name = "All IDs"

As the macro creates a sheet named "All IDs" there can be no sheet in the "Master file" with the same name. That maked the macro throws an error as you got so you need to remove that sheet.

Asfor the empty files well that's my bad. You need to add a line activating the "All cases" sheet so change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

then the macro should run without any problem.

I would also sugges you test the code from bakerman2 as his code seems better than mine.

hi bakerman2 just had a quick look at your code seems shorter (better) than mine will have a closer look later this evening.

Alf

Alf and bakerman2,

Thank you for your help! Your macros are working fine. Sorry for the late reply-was out of pocket.