How to create multiple Excel workbooks from a worksheet with VBA

Question

I have a huge Excel report with many different server names and now I thought it might be possible to write a macro in VBA that creates a new excel file which is named after the server name.

For example my table has a column called "servername" and n rows which contain the name "Server1" and m rows which contain the name "Server2"

Now I want the macro to create two files, Server1.xlsx and Server2.xlsx and both files contain every row which contains the servername.

Is this possible? If yes could you please help me I have never used VBA before and dont know how to start.

Yes this is possible. But please note that because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading [ask] might help you to improve your question. — Pᴇʜ, Mar 11 '19 at 14:25
Please read my question again, I didnt ask you to write my code for me, I just asked if something like I have asked is possible and if you know where I could start... — 0xDr0id, Mar 11 '19 at 14:38
Then please read [ask] again to understand that this question is too broad to give a good answer. Start with reading some introduction tutorials on VBA first to get used to the basic syntax. Then proceed with doing some research on how to filter data (hint `Autofilter`) and how to copy filtered data to a new sheet. Then try it on your own and come back with your code and something you have tried. • A good idea could be to use the macro recorder at first and applying [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Mar 11 '19 at 14:45

score 0 · Accepted Answer · answered Mar 11 '19 at 16:53

You start by creating your two workbooks. You now have three books. The original (which holds the program code) is referred to as ThisWorkbook, the other two are referred to by object variables. Then you start looping through each row in your existing page, and use an IF statement to take each row to either one or the other destination. You stop when you reach a blank row. Here's an example (note you will have to expand this yourself)

 Sub Example()
 Dim wb as workbook 'declare an objvet variable
 Set wb = workbooks.add()  'point it to a new instance of the workbook class
 wb.saveas "File1.xlsx" 'save it as file1
 dim source as Range  'pointer to original file
 dim target1 as range  'pointer to destination
 set Source = thisworkbook.worksheets(1),range("a1")  'point to a1 in source file
 set target = wn.worksheets(1).range("a1")
 Do  'start a loop
 if source.text = "fred" then  'id cell holds this text then....
     source.entirerow.copy target  'copy source to target
     set target = target.offset(1,0)  'move target pointer down one row, no columns
 end if
set source = source.offset(1,0) 'move source pointer
loop until source = "" 'stop at first blank cell
wb.save

score 0 · Answer 2 · answered Mar 11 '19 at 17:09

It's entirely possible. However, if you haven't written VBA before, you'll need to start there first.

I recommend Bill Jelen's Excel VBA books. They're very informational and he is the top authority on all things Excel/VBA. If you don't want to pay, there is always Chip Pearson's site.

After you've developed a beginner's grasp of VBA, I'd recommend storing all of your metadata in an Excel Table, loop through all rows in that table and create workbooks using the metadata in each row.

How to create multiple Excel workbooks from a worksheet with VBA

2 Answers2