Excel 2010-VB Macro formula into many spreadsheets [Solved/Closed]

AnFey 1 Posts Monday February 24, 2014Registration date February 24, 2014 Last seen - Feb 24, 2014 at 06:40 AM - Latest reply:  AnFey
- Mar 3, 2014 at 06:19 AM
Hello Everyone,

I am new in VB programming and I am trying to write a macro in Excel. Basically, I have written a formula in Excel which enable me for a certain name to extract data from a software into one spreadsheet. But I have a spreadsheet with 600 names. I want to write a VB code which will enable me to cycle through this list of names and dumps these data into separate sheets automatically. In other words, I want to call the excel formula I have written, for all the names from my spreadsheet. For each name, I will have a separate spreadsheet. Is it possible?

Thank you for your help and all your suggestions are greatly appreciated,

anfey
See more 

4 replies

TrowaD 2438 Posts Sunday September 12, 2010Registration dateModeratorStatus October 16, 2018 Last seen - Feb 25, 2014 at 10:49 AM
0
Thank you
Hi Anfey,

It's not entirely clear to me what you want to do. But to cycle through a list you can use:
For each cell in Range("your range")
if "your criteria" then "copy row or cell to another sheet"
Next cell

Let me know where you get stuck.

Best regards,
Trowa
0
Thank you
Hello Trowa,

Thanks for your answer. Since the time I posted my question, I went through some tutorials to learn more about VBA and I have already done what you told me to do.

However, I did not manage to do everything I wanted to do. Let me explain. When I go through this list of cells (600 cells), I want to open a new spreadsheet for every cell and put the related data into this opened spreadsheet. What I have already done is that I opened "manually" 600 spreadsheets (by typing Maj+F11, 600 hundred times) and I run my VB code to fill the 600 spreadsheets.

Is there a way to open automatically the 600 sheets by using a VBA code? In other words, I am looking for a sample VBA code that opens a new spreadsheet automatically, I will adapt it with what I am doing.

Thanks a lot,

AnFey
TrowaD 2438 Posts Sunday September 12, 2010Registration dateModeratorStatus October 16, 2018 Last seen - Feb 27, 2014 at 10:35 AM
Hi AnFey,

If your sheets are already created use:
For each cell in Range("your range") 
Sheets(cell.Value).Activate
Next cell

If they still need to be created then use:
For Each cell In Range("your range")
Sheets.Add.Name = cell.Value
Next cell

Let me know if I can assist further.

Best regards,
Trowa
Hello Trowa,

Thank you very much for your answer. It has been of a great help.

Best regards,

AnFey