Excel VBA: Create New Worksheet For EACH UNIQUE ITEM in List & Copy Record

This video tutorial features VBA code that creates a new worksheet for each unique item that it finds in a list and then copies that record to the new sheet.

In the featured scenario we have a thousand transaction records. The VBA code creates a new worksheet for each branch location that it finds in column D and then copies the record to that sheet. We end up with a sheet for each branch containing all the records for that branch.
------------------------

[James-wlbt]

This is exactly what I was looking for thank you very much !! Really saved me with this

[guganeshanmahalingam]

Hi, Great Tutorial. I have successfully created worksheets with your method. I have seen your other video "Excel VBA to Convert Range to Table". I like to combine the two methods for my use case. Is it Possible to create table for worksheet range?. If So, Kindly Guide me . Thanks

[saurabhsrivastava]

Hey.. nice explanation..can you make a video that how we can do this on different workbooks(not worksheets) in a folder.

[IvanCortinas_ES]

Excellent video Chester!! Thank you so much for sharing it. It is very practical.

[gabbyiem]

If i wanted to just copy and paste the entire row of data and the entire header, what would I change those lines of code to be? Rather than the offset.

[patrickopaola]

Chester great. It is what I am exactly looking for

[aliceramos]

Hey Chester, great video. I'm receiving an error message with the following line " BranchName.Offset(0, -3).Resize(1, 13).Copy Destination:=Worksheets(BranchName.Value).Range("A1").End(xlDown).Offset(1, 0). I'm looking up to information column A. What do I need to change?

[mickeycell]

This is what I want!!!! Thank you very much!!!
If Portsmouth has a special character with "\"? How to remove this "\" when creating the new worksheet name? Appreciate if you teach me on this part?

[kurtlandeshong]

I stumbled upon this video that is exactly what i want. I however get an error when i try to run the script on my data set. "Sub-script out of range". It seems as if the script will not run if the data in the column is a number. Any ideas on how to get around this?

[jdozen]

Hi sir what if I want that the SHEET NAME will be named based on your Transaction ID. Please advise. Thank you!!

[grenemyr]

I used this macro on a PC and it worked beautifully for both your original macro and the one I altered to match my data. However, when I copied the same macro into a MacBook, using excel, of course, it bombed out. Is there some code needed to work inside mac?

The error says "Name Already Taken. Try a different one."

Again both versions of code worked perfectly on PC....

[James-wlbt]

Quick question: I’ve got 5 columns labelled “ address” “ location” “ works” “ action” and “ completed”. I’ve managed to successfully follow your code to get a new tab for each unique address. However, I need to make a further edit and I’m struggling. I only want to make a new tab for each unique address if the “completed” column = N
Any ideas on how i can do this ?

[kbillan]

This is a great vid! I have one issue, mind you, this is the first time I've tried writing in VBA. I have a set of data that I am trying to sort by week number. I converted my dates using =weeknum, can't get the sheet to run right at all, even just doing this in your sheet. I got it to work by adding text before the output, ="week "&(weeknum(cell)). This runs, but sorts funny. Week 1, Week 10, 11, 12... Week 2.. so the tabs are also all silly. Even putting in a sort macro will return the goofed up numbering. Any suggestions as far as how to do what you've done here but based on week number?

[kandhar]

Hi ChesterTugwell....
hope you're doing good..
I have one quetion here. If I run this code it's working completely fine with creating more than one sheet. But if I have only one name in the Data sheet and run macro to create or update. I recieve run time error 1004 - Method "Name of object'_Worksheet Failed and I see new empty sheet created.. Please help me on this case.

[ecto_hub]

Question: What if i need to add an additional range from a different worksheet to this? I tried adding new sheet and ranges tot he script but i get an error.

[andrewdale]

Hello. Is there a download version of your code? I've tried to follow it but with no luck. I've never used VBA before 😂

[shalinireddy]

Hello Chester,
I have a sheet of 56 - 60 columns data information in it.

From that sheet I only need 3 column data i.e., Preferred Name, Email Address & Termination Date. The poison of the headers changes each time when we receive the data.

So, I want to create a macro based on the "headers" instead of column number to copy data from the existing sheet to the new work book.
Please help me!

HI can you tell me how to add path i'm not able to add

[NelsonNaves]

how to duplicate a worksheet with new name in userform¡?