Create a worksheet for each uiniuqe item in my Data range?

[rpaulson]

I see a lot of request for some VBA code to create individual sheets out of my data. (Like: "I want a sheet for every part number in my list")
I put together some code to automate the process for you.

Enjoy!

Sub Create_Worksheets()
'This macro will seperate the data in  your range into individual worksheet(s)
'it will look in Column "A" of every row in your data
'it will then add that row of data into a worksheet whose sheet name is the value in columnn "A"
'if the worksheet does not exist, the code will automatically create and name the new sheet for you.

Dim rs As Worksheet
Set rs = ActiveSheet

For r = 1 To rs.Range("A" & Rows.Count).End(xlUp).Row
wsName = rs.Cells(r, "A") '<<<<<<<<<<<<<<<<<<<<<  Change this to the column that you would like to seperate
If wsName = "" Then GoTo 10

If WorksheetFunction.IsErr(Evaluate("'" & wsName & "'!A1")) = "True" Then Sheets.Add.Name = wsName     'if true then sheet does NOT exist, if False then sheet does exist

wr = Worksheets(wsName).Range("A" & Rows.Count).End(xlUp).Row + 1
rs.Rows(r).Copy Destination:=Worksheets(wsName).Range("A" & wr)

10 Next r
rs.Activate
MsgBox "Done"

End Sub

-Ross

 

[denzo36]

Hi Ross, thank you for that, I will definitely make use of this.

As a VBA learner, can I ask you about syntax on the line:
If wsName = "" Then GoTo 10

Then later on you have:
10 Next r
rs.Activate

What does the '10' represent in your 'GoTo 10' bit

 

[rpaulson]

the 10 is just a line number. If you want you can put line numbers on you code.
the If wsName = "" just check to see if the cell is blank. if it is blank then the code jump to line 10 which goes to the next row of you data.

 

[denzo36]

Cool I did not know that.

Can you assign any number for that or does it have to be the actual line i.e. if you just arbitrarily said 'Then GoTo 30' and then had '30 Next r' would that also work even though that might not be the 30th line in your code?

 

[rpaulson]

the line number can be any number you want. it has nothing to do with the actual number of lines in you code.

ie:
if name="Fred" then goto 9999
...
......
...
9999 exit sub

 

[denzo36]

Cool, thanks Paul, every day is a learning day

 

[rjmdc]

I see a lot of request for some VBA code to create individual sheets out of my data. (Like: "I want a sheet for every part number in my list")
I put together some code to automate the process for you.

Enjoy!

Sub Create_Worksheets()
'This macro will seperate the data in  your range into individual worksheet(s)
'it will look in Column "A" of every row in your data
'it will then add that row of data into a worksheet whose sheet name is the value in columnn "A"
'if the worksheet does not exist, the code will automatically create and name the new sheet for you.

Dim rs As Worksheet
Set rs = ActiveSheet

For r = 1 To rs.Range("A" & Rows.Count).End(xlUp).Row
wsName = rs.Cells(r, "A") '<<<<<<<<<<<<<<<<<<<<<  Change this to the column that you would like to seperate
If wsName = "" Then GoTo 10

If WorksheetFunction.IsErr(Evaluate("'" & wsName & "'!A1")) = "True" Then Sheets.Add.Name = wsName     'if true then sheet does NOT exist, if False then sheet does exist

wr = Worksheets(wsName).Range("A" & Rows.Count).End(xlUp).Row + 1
rs.Rows(r).Copy Destination:=Worksheets(wsName).Range("A" & wr)

10 Next r
rs.Activate
MsgBox "Done"

End Sub

-Ross

what if there is a page already? will it update the page or stop the code?

 

[rpaulson]

it will update the page and continue.

 

[rjmdc]

i am so stumped. i need a bit more help
i have spent nearly 50 hours and messing up workbooks and worksheets
i need to split the master sheet:
1- into tabs (after master sheet) based on column B names
2- also create separate workbooks for those very same names
3- but each workbook has to further split their page into tabs filtered by date in column E and label tabs with date

can this be done?

 

[rjmdc]

i dont get header row?