Excel 2010-VB Macro formula into many spreadsheets

[Solved/Closed]
Report
Posts
1
Registration date
Monday February 24, 2014
Status
Member
Last seen
February 24, 2014
-
 AnFey -
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

2 replies

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
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
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
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
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