VBA

[twildone]

Hi....I am trying to create a new worksheet with the rows of Data in the worksheet "Master" if the selected item is in column F. When I click on the Submit button, a worksheet is created but no data is populated. I get the error that I have bolded below....can anyone suggest what I missing....thanks.

Private Sub Userform_Activate()
cboTypeStatus = ""
Dim xCell As Range, NoDupes As New Collection, Item
uRows = Sheets("Master").Cells(Sheets("Master").Rows.Count, "F").End(xlUp).Row
For Each xCell In Sheets("Master").Range("F2:F" & uRows)
On Error Resume Next
NoDupes.Add xCell.Value, CStr(xCell.Value)
Next xCell
For Each Item In NoDupes
Me.cboTypeStatus.AddItem Item
Next Item
End Sub

Private Sub cmdSubmit_Click()
Dim d As String
Dim X As String
Dim i As Long
Dim Y As Long
Range("F1").Value = cboTypeStatus
cboTypeStatus = Empty
frmTypeStatus.Hide

X = cboTypeStatus
[COLOR=rgb(0, 0, 0)][B]Sheets.Add.Name = Replace(CStr(X), ":", " ")[/B][/COLOR]
Sheets("Master").Activate
d = Range("F1").Value
i = 1
Do Until ActiveCell.Value = d
Cells(1, i).Select
i = i + 1
Loop
Rows(1).Copy Sheets(Replace(CStr(X), ":", " ")).Range("A" & Rows.Count).End(3)(2)
Sheets(Replace(CStr(X), ":", " ")).Range("A1").EntireRow.Delete Shift:=xlUp

Sheets("Master").Activate
For Y = 2 To 100000
If Cells(Y, ActiveCell.Column) = X Then
Cells(Y, ActiveCell.Column).EntireRow.Copy Sheets(Replace(CStr(X), ":", " ")).Range("A" & Rows.Count).End(3)(2)
End If
Next Y
Sheets(Replace(CStr(X), ":", " ")).Activate
Cells.Select
Selection.Copy
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues
Do Until Cells(1, 1) <> ""
Cells(1, 1).Select
Cells(1, 1).EntireColumn.Delete Shift:=xlToRight
Loop
i = 1
For i = 1 To Worksheets.Count
Worksheets(i).Tab.ColorIndex = i + 10
Next
 End Sub

 

[StephenCrump]

I don't know what you do want the new worksheet name to be?

But at the moment, your code is trying to give a blank name to the new worksheet, hence the error:

cboTypeStatus = Empty
X = cboTypeStatus
Sheets.Add.Name = Replace(CStr(X), ":", " ")

 

[twildone]

Hi....I was trying to get the value that was selected from populating on the sheet where I have a command button. I removed the statement cboTypeStatus = Empty and sheet is created and populated but I still get the value that I have selected populating in cell F1 of the sheet where the command button is located.

 

[StephenCrump]

Sorry, I don't understand either of these comments:

Hi....I was trying to get the value that was selected from populating on the sheet where I have a command button.

... but I still get the value that I have selected populating in cell F1 of the sheet where the command button is located.

Perhaps you can explain in a bit more detail:

- what is happening
- what you want to happen?

 

[twildone]

Hi Stephen,

What is happening is when I select the item from the combo box and click the Submit button, a worksheet labelled with the name of the Select item is created and populated with all the records that match the selection criteria. This is what I want and is working fine. But also, the name of the selection item is also filling in the cell (in this case is F1) on the worksheet where I have a command button that I click on to search for this item. This is the part that I don't want and I am trying to keep this selected item from filling cell F1.

 

[StephenCrump]

You have this line in your code:

Range("F1").Value = cboTypeStatus

 

[twildone]

HI Stephen.....I deleted that statement that you referred to and that fixed the problem...thanks a ton.