Creating New Sheets from Main Sheet

[samfarrugia]

Hi,

So, i'm new to excel and perhaps someone can point me in the right direction; what I should be looking at.

I have an excel sheet, with data on it.

This data has a colomn 'N'; which specifies a 'Group' (North, South or East) (data of groups via 'vlookup' from sheet 'CLIENT LIST')

What I am trying to do, is to be able to split the data on three seperate excel sheets automatically. (OR even better different workbooks are created)
'NORTH CLIENT', 'SOUTH CLIENT' and 'WEST CLIENT' (see the tabs which are colour coded GREEN - (this would be the end result)) and the name of each single sheet is renamed to the 'CLIENT GROUP'.

Any ideas?

Thanks.
Sam

[rvasquez]

Hello there,

Attached is your original workbook updated to include a macro that I believe will accomplish what you are trying to achieve.


If you haven't worked with macros you will need to enable macros or content when you open the workbook. Also you will need to change the filepath in visual basic before running the macro.

To do this hold down the Alt key and press F11 on your keyboard.

I have inserted a comment in the code that tells you where to change the file path. Change the text within the quotes and before the code & ActiveSheet.Name & ".xlsm" _ to your filepath.

Close Visual Basic and then select the developer tab. Select the Macros option and then select CopytoNew macro. Select the run option.


Let me know if you have any questions.


Thanks!

[samfarrugia]

Hi. Thanks for your help.
Macros enabled and I amended the file name.
I still have a problem though; the new excel files are created and saved; however there is no data on the sheets.

Regards
Sam

[samfarrugia]

Formatting is coming up, but no text.

[rvasquez]

Hmmm that's odd from my end it's working. Can you please copy the code you altered and paste it? Thanks!

[samfarrugia]

See file attached.

[rvasquez]

Hey there,

The file you sent me has no data just formatting in the worksheets. So that would explain why when you open the individual documents they have just formatting. There are no values in the worksheets. Add text/data into the individual worksheets and it should work.

[samfarrugia]

The data would be in the main sheet only, and the data depending on the group has to be copied...

[rvasquez]

In the original file your uploaded there was data in all the worksheets? The macro I gave you only copies each worksheet after the 2nd one in the workbook and saves it to the location specified. So if there is no data in the worksheets then the new files will not contain data. Are you wanting to copy some information into these worksheets first and then save the files? If so I can adjust the macro for this.

[samfarrugia]

Yes exactly..
the data in the main sheet, is copied into the different sheets according to the group.

[rvasquez]

Okay,

Try this one and let me know if it works!

Thanks!

[samfarrugia]

Works perfectly. One last thing though, is it possible that the new workbooks are not macro enabled workbooks?

[rvasquez]

Alright! Glad we got that to work for you!

Now, I've updated the code to delete the macros but there's a couple of other things that you might get an error on and may need to adjust as I'm not sure they'll transfer to your computer.

Problem #1:

Before running the code go to Visual Basic by holding down the Alt key and pressing F11. Then, on the main menu bar select the Tools Option. Then Select References. Make sure the Microsoft Visual Basic for Applications Extensibility 5.3 option is checked. It should be one of the top ones if it is. If not search through the list and check the box next to and then click Okay. You can then close out of VBA.

If this is not done the code will throw an error and the code will not execute.

Problem #2:

Another problem with referencing VBA code through VBA is your settings. So on the excel main menu select the File option. Then select Options. From here select Trust Center. Then select Trust Center Setting, this is not on the left side it's withing the text. Finally select Macro settings and make sure the box next to "Trust access to the VBA project object model" is selected. Press okay and you can exit.

If this is not done the code will throw an error saying you don't have access and will not execute.


If these things are done the code should perform correctly.

Let me know if you have any questions or problems.

Thanks!