Creating New Spreadsheet based on column - Macro Help! [Closed]

Report
Posts
4
Registration date
Friday July 17, 2015
Status
Member
Last seen
August 4, 2015
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello,

This is my first time trying to use a Macro, so please bear with me. Anywho, I am using Excel 2007, if that helps. I have a spreadsheet that has a lot of data in it, and one of the columns is called event. I have 11.01 through 11.20 and I need to write a macro that looks at that column, and takes all the rows that have 11.01 in that column, copy it, and paste it in a new spreadsheet, and label it as (event name.today's date), then 11.02 and repeat all the way until the cells in that column are blank, and then stop. I have never written a macro before, so any help you guys can provide would be GREATLY appreciated! I have read some of the other postings and forums here, and you guys seem to know your stuff!

Thanks in advance!

6 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
create a sample file with small amount of data

upload this fie to speedyshare.com and inform the download web address

then explain what you want with respect to the file
Posts
4
Registration date
Friday July 17, 2015
Status
Member
Last seen
August 4, 2015

I am at work unfortunately and can't upload to a file share site. Here's what it looks like:

Header 1 Header 2 Header 3 Event Header 4
Data Data Data 11.01 Data
Data Data Data 11.01 Data
Data Data Data 11.02 Data
Data Data Data 11.02 Data
Data Data Data 11.02 Data
Data Data Data 11.03 Data
Data Data Data 11.03 Data


I need a formula, that looks at the data under the "Event" column, and will copy all of the data for Event 11.01 and paste it into a new spreadsheet, and save it as an excel file under the .xls format. Once it has done that with 11.01, I want it to move onto 11.02, and do the same steps, and repeat until the Event column has nothing in it. I have 1 big file that needs to be split out into multiple files. Please let me know if you need anything else.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
because your file is big and you are opening new sheets it may take some time. opening of three sheets takes 10 seconds. anyhow try the macaro "test"
download the file from

http://speedy.sh/RPGFH/renntek-150729.xlsm

and run only "test"(though there are two macros in the vb editor)

your comments please
Posts
4
Registration date
Friday July 17, 2015
Status
Member
Last seen
August 4, 2015

venkat1926, it works very well, however is there a way to have the code create a new file instead of a tab for each event?

Thanks!
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790 >
Posts
4
Registration date
Friday July 17, 2015
Status
Member
Last seen
August 4, 2015

why do you want new file for each event. There will be too many separate files and each file will have only one sheet. not that it cannot be done. please review your requirement
Posts
4
Registration date
Friday July 17, 2015
Status
Member
Last seen
August 4, 2015

For my job, the data needs to be split out onto separate excel files. It's ok if they have 1 sheet per file, there will be other data that has to be added in manually later. Can this be done?
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
ok I shall modify the macro and send it to you in about 24 hours
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
BE CAREFUL. The different files like 11.01 are parked in a folder E:\tempo. This folder SHOULD NOT CONTAIN ANY OTHER FILES because for repating the macro the files in this folder are KILLED.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
790
acknowledge receipt of this WARNING