Loop through a Filtered List

[EggHead]

Hello,

I am trying to write a macro that will let me loop through an auto-filtered list (column C in example file) . I want to filter for a criteria (in this case a network), copy the filtered data, paste it onto a new workbook and save it. Although I have been able to record a macro (see below) that can do this, I have to copy and paste this macro over and over again for the other networks. I would like to know if there is a macro that can loop through each criteria in an auto-filter list, copy and save the data into new workbooks for me. I've attached a sample workbook that should be helpful in understanding what I'm trying to do. Any help is much appreciated.

Please Login or Register  to view this content.

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

I have removed the unnecessary wording

How can I

[royUK]

Try this, it will add a new book for each item in Column 2 of the table

[royUK]

Why did you change the title to again add unnecessary words?

[EggHead]

Thanks! Worked like magic!

[royUK]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You can also rate the answer(s) using the scales icon

[EggHead]

Just one more question. How can I change the coding so that I can specify where I want to save the files? For example how could I tell the code to save the files in a folder called "ATTD" on my desktop?

[royUK]

Try this amended code,it will save to C:\TestMacro. Change to your path where I have indicated in the code. I have also added a line to close the new workbooks

Please Login or Register  to view this content.

[EggHead]

Do you know why I get an error message if I try to copy and paste the code to another workbook with the same setup but different content? When I try to debug the problem this code is highlighted in yellow: wsNew.Name = sNm.

[royUK]

Do you know why I get an error message if I try to copy and paste the code to another workbook with the same setup but different content? When I try to debug the problem this code is highlighted in yellow: wsNew.Name = sNm.

It should work in the new workbook if you have the same layout, you would need to attach the file so that I can see why it is happening. One possibility is that the workbook contains a sheet with the same name

[EggHead]

Hey,

I've attached the workbook. Thanks for the help by the way. I really appreciate it.

-EggHead

It should work in the new workbook if you have the same layout, you would need to attach the file so that I can see why it is happening. One possibility is that the workbook contains a sheet with the same name

[royUK]

You haven't adjusted the code correctly. The data for rData wasn't defined correctly & assuming you want a report for each school then you were using the wrong Column

Please Login or Register  to view this content.

[nizamvk]

Hi All,

If one row there are more than 130 characters then only 130 should remain in that row & rest of the characters should be copied to next row.
Note that all the other contents in the row should be copied to next row with the rest of the remaining 130 characters.

See the attached example.

Thanks is advance.

Regards,
NVK

[mtsai617]

Hi All,

I am using Roy's codes but have realized that it is difficult to do it right when you have a large set of data. In other words, Excel appears to be "pasting" the incorrect rows to the new tab, and I have no idea why is causing that. Does anyone know if there is a way that I could fix that?


Many thanks,

Meng

[ConneXionLost]

nizamvk,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

[ConneXionLost]

mtsa617,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.