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

Posts
1
Registration date
Monday February 24, 2014
Last seen
February 24, 2014
- - 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 

2 replies

Posts
2441
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 18, 2018
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
Posts
2441
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 18, 2018
-
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