MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Creating new worksheets from field

  Asked By: Ryan    Date: Nov 02    Category: MS Office    Views: 2049

I've got
everything working to create a job shop worksheet for each part on a
master material list. I click a button after getting the list right,
and it creates a sheet for each part, and at the end brings the
master sheet back active. I've got a few problems. One is how can I,
at the end of the script, have it sort the sheets in alphabetic
order. I have another script that will do it, but I have to run it
separate. Second, I'd like it to only create sheets with new part
numbers, rather than replacing all sheets if I run it more than
once. I'll paste the code if that helps.

Sub PagesByDescription()
Dim rRange As Range, rCell As Range
Dim wSheet As Worksheet
Dim wSheetStart As Worksheet
Dim strText As String

Set wSheetStart = ActiveSheet
wSheetStart.AutoFilterMode = False
'Set a range variable to the correct item column
Set rRange = Range("a4:o4", Range("a1000:n1000").End(xlUp))

'Delete any sheet called "UniqueList"
'Turn off run time errors & delete alert
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("4 UniqueList").Delete

'Add a sheet called "4 UniqueList"
Worksheets.Add().Name = "4 UniqueList"

'Filter the Set range so only a unique list is created
With Worksheets("4 UniqueList")
rRange.AdvancedFilter xlFilterCopy, , _
Worksheets("4 UniqueList").Range("a1"), True

'Set a range variable to the unique list, less the
Set rRange = .Range("a2", .Range("a65000").End

End With

On Error Resume Next
With wSheetStart
For Each rCell In rRange
strText = rCell
.Range("a2").AutoFilter 1, strText
'Add a sheet named as content of rCell
Sheets.Add(Type:="worksheet").Name = strText
Range("C2").Formula = strText

Next rCell
End With

With wSheetStart
.AutoFilterMode = False
End With

On Error GoTo 0
Application.DisplayAlerts = True
End Sub



4 Answers Found

Answer #1    Answered By: Bu Nguyen     Answered On: Nov 02

You seem to have achieved a lot already!

The easiest way of getting the sheets  in order would be to call the sorting
macro you already have from the end  of the main macro. Just put the name of
the sort  routine in as a line in PagesByDescription.

After the line 'Add a sheet  named as content  of rCell you could put

On Error Resume Next 'actually I think you already have this running
If ActiveWorkbook.Sheets(rCell) Is Nothing Then
'Your code  if the sheet does not exist
'your code if the sheet does exist
End If

Answer #2    Answered By: Alonzo Roberts     Answered On: Nov 02

That worked for sorting it, but I'm not sure what my code  should be if
the sheet  does exist. Any guidance?

Answer #3    Answered By: Dion Jones     Answered On: Nov 02

It depends what you want to do with it. I cannot tell from your code  what
that is.

But I suppose in essence it will be similar to what you do with a new sheet
after you create  it.

Answer #4    Answered By: Lurleen Fischer     Answered On: Nov 02

You set  me on the right  path. I got it figured out. Got another question, but
should probably ask it in a different thread, and at a little later time.

Didn't find what you were looking for? Find more on Creating new worksheets from field Or get search suggestion and latest updates.