Copying from master worksheet and create new worksheet from cell value

[lzhenl]

Hi,


I have an excel file which I would like to create a macro / code to create new worksheet based on cell value for the Column A.


Attached is my sample file.


https://dl.dropboxusercontent.com/u/172679/Book%201.xls


The worksheet is the Master worksheet which I will update regularly.

I would like Excel to create a new worksheet with the cell values in Column A, ie. Fruit, Location.

The respective worksheet will contain the corresponding data in Column B and C.

Is this possible?


Best Regards,

Zhen

 

[Smallman]

Hi Izhenl


Put the following into a regular module in the workbook you presented. Run it from the Master sheet. I didn't make the first line dynamic so watch out for this if rows greater than 1000 or greater than 65K for other lines.

[pre]

Option Explicit

Sub Move()
Dim i As Integer
[a1:a1000].AdvancedFilter 2, [F1], , True
For i = 2 To Range("F" & Rows.Count).End(xlUp).Row
[a1:a1000].AutoFilter 1, Range("F" & i)
Range("B2", Range("C65536").End(xlUp)).Copy Sheets(Range("F" & i).Value).Range("A65536").End(xlUp)(2)
Next i
[a1].AutoFilter
End Sub

[/pre]

Take care


Smallman

 

[SirJB7]

@Smallman

Hi!

Am I missing something or the code doesn't work? Getting undefined extraction range in 1st executable statement (advanced filter method line).

Regards!

 

[Smallman]

SirJB7


Welcome back!!! You have put the code into a worksheet module. That is my guess anyways. I just put it in a regular module put


Data

Cat

Dog

Princess


in Col A of sheet one and had three sheet names which matched, so just to be clear Cat, Dog and Princess.


The code went like Coffee and a muffin on a cold Brisbane Morning!!!!!!! Goes well and I will send you a file to prove it if you like? I can't post files to the net in my current location but have email access : )


Take care


Smallman

 

[SirJB7]

@Smallman

Hi!


Thanks for the welcome, but there were just a few days, short vacations taking advantage of a non-working Monday in my country.


You missed your guess, buddy. I put the code in a new standard module (Module1), and:

a) Run from the VBA editor with MASTER worksheet active, it displays the mentioned error.

b) Run from a command button added to that worksheet, it doesn't crash but moves the whole MASTER worksheet to a new workbook keeping the remaining worksheets in it and not adding any newly created ones.


This is the link to the sample file:

https://dl.dropboxusercontent.com/u/60558749/Copying%20from%20master%20worksheet%20and%20create%20new%20worksheet%20from%20cell%20value%20-%20Book%201%20%28for%20lzhenl%20from%20Smallman%20at%20chandoo.org%29.xlsm


Regards!

 

[Smallman]

Hi SirJB


I will just post the workbook on this site tonight when i have access to file sharing. It will be with the Ops data, the code will be unchanged and it will go like thunder.


Take care


Smallman

 

[Smallman]

Here is the file I promised. I have not changed one character from the above coding. Used Ops file. Goes - fine!


http://rapidshare.com/files/1811483991/Smallman.xls


Take care


Smallman

 

[lzhenl]

Hello!


Sorry, I didnt reply earlier as I was down with a nasty flu last week

I had tried putting the code into the module but nothing is happening.

I had also downloaded your sample file, and tried to entering new category, but nothing happened either - even when i saved the data and reopen the file.

Could it be my Excel version? I am using Excel 2003 now.


Best regards and take care too

Zhen