Posts by ~V7C~

Re: Creating new worksheets for each cell value under specified column

That worked perfectly, thanks Stephen!

Now I am trying to extend the codes to, after creating and naming the new worksheets, copy all rows in the MasterSheet where the Class data is "A" to the newly created Worksheet "Class A", then all rows containing "B" under "Class" column into Worksheet "Class B", and so forth. Stuck again after an hour of trying however... further help would be much appreciated.

Re: Creating new worksheets for each cell value under specified column

Quote from StephenR;773578

You're asking me?! It's a variable you put in your code so it needs a value otherwise it is just null or empty. I don't know what it represents so I don't know what value it should be.

Let's take a step back - explain in plain English what you are trying to achieve. It's not completely clear to me from your example. My best guess - are you saying you want a list of unique values of column B and a separate sheet added for each containing the corresponding column A values?

Sorry for the late reply, I have been really busy for the past few days :dead:

Under Column B, there are four unique values: A, B, C, and S. I want to create 4 new worksheets that are titled "A", "B", "C" and "S", respectively.

Re: Creating new worksheets for each cell value under specified column

Quote from StephenR;773537

Yes, but you also need to assign it a value. At the moment you are not doing this.

I don't quite understand what value need to be assigned to ClassString?

Totally new to VBA here

Re: Creating new worksheets for each cell value under specified column

Quote from StephenR;773526

OK, so a couple of things. What is ClassString? There is no data in column M of your file.

By the way, a good practice tip on declaring variables http://www.cpearson.com/excel/declaringvariables.aspx

Sorry, I forgot to say that it should be column B in my sample file instead of column M.

Following your advice, I will define ClassString (as string?) as see if that can get my code to work...

Re: Creating new worksheets for each cell value under specified column

Quote from StephenR;773515

The first thing I note is that you don't define ClassString. I can't see anything being copied either. Could you post a sample workbook - makes life a lot easier?

Sure

forum.ozgrid.com/index.php?attachment/69610/

Hi everyone. In Sheet1 of my workbook, for each unique cell values under Column M (those values are 'alphabets', e.g. "A", "B", "C", etc.), I want to create new Worksheets titled "Class [cell value]", then copy the Sheet1 rows containing the relevant cell values ('alphabets') to the corresponding new worksheets. The problem I am facing now is that I end up creating a single new worksheet titled "Class ". Would appreciate if anyone can help.

Private Sub Test_Click()Dim Ws As Worksheet
Dim i As Integer
Dim ClassRange As Range
Dim ClassCell As Range
Set ClassRange = Sheet1.Columns("M")
For Each c In ClassRange
            
    For Each Ws In Worksheets
               
        If LCase(Ws.Name) = ClassString Then
            
            Sheet1.Range("Q1").Value = "Sheet Found"
            Exit For
                    
        Else
            
            Sheet1.Range("Q1").Value = "Class " & c.Text
                                           
        End If
    
    Next Ws
            
    If Sheet1.Range("Q1").Value = "Sheet Found" Then
    
        Sheet1.Select
    
    ElseIf InStr(1, Sheet1.Range("Q1").Text, "Class", vbTextCompare) = 1 Then
    
        ActiveWorkbook.Sheets.Add Before:=Sheets(Sheets.Count)
        ActiveSheet.Name = Sheet1.Range("Q1").Text
        
        Sheet1.Select
        
    Else
    
        MsgBox ("Error!")
        Exit For
    
    End If
    
Next c
End Sub

Re: Modify properties of multiple comboboxes

Quote from royUK;738936

Which kind of controls are you using, Forms or ActiveX?

ActiveX

I have 10 Comboboxes on the same Worksheet. I named the first 5 of them "P1A1" to "P1A5", whereas the latter 5 were named "P2A1" to "P2A5".

If I want to hide all 10 comboboxes at once using a command button, here's the current code I am using:

Private Sub CommandButton1_Click()
Sheet1.P1A1.Visible = False
Sheet1.P1A2.Visible = False
Sheet1.P1A3.Visible = False
Sheet1.P1A4.Visible = False
Sheet1.P1A5.Visible = False
Sheet1.P2A1.Visible = False
Sheet1.P2A2.Visible = False
Sheet1.P2A3.Visible = False
Sheet1.P2A4.Visible = False
Sheet1.P2A5.Visible = False
End Sub

I wonder if they are ways to simplify the codes above (using loops for instance) since I plan to add more comboboxes with similar naming conventions.

Re: Find all Cells containing a string that matches the string in a designated cell

Thanks philby! After some experimenting, I got it to work using your first method which involves For Each... Then.

However VBA kept giving me error messages for the following code line:

Set myrange = Worksheets(2).Range("B3", ("B3").End(xlToRight)

I was attempting to use this code line to compare a region of string-containing cells in Sheet 2 (these cells are all in row B, starting from B3) against the string in my A1 cell in Sheet 1. How should I go about doing this?

First of all, I link cell A1 of Sheet 1 to a Combo Box whose list contains a series of strings. Now after I pick one of the strings from the Combo Box's list, I want to use VBA to find all the cells in Sheet 2 that contain strings matching the one I selected from the Combo Box list. How do I this?

I am aware of the Cells.Find function:

Cells.Find(What:=, After:=ActiveCell, LookIn:=xlValues, LookAt:= _        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , MatchByte:=False, SearchFormat:=False).Activate

I tried to specify Cell A1 of Sheet 1 after the "What:=" line above but VBA keeps giving me error messages. Is there a workaround?