Need Code edit - 2

[VBABEGINER]

Can any one tell me what goes wrong in this code..

LblUname = Label taken on userform

Dim x As Integer
Dim y As Integer
Dim NewSh As Worksheet


y = Sheets.Count


For x = 1 To y


If Sheets(x).Name = LblUname Then 


    NewSh = ActiveSheet.Name


    Set sht = Application.Workbooks("Timesheet.xlsm").Sheets(NewSh)
    newrow = sht.Cells(Rows.Count, 1).End(xlUp).Row + 1
    sht.Cells(newrow, 1) = CDate(Me.txtstartdate)
    Closed_date = DateTime.Now
    sht.Cells(newrow, 2) = CDate(Closed_date)
    '---------------------------------------------
    sht.Cells(newrow, 3) = Me.cmbActivity   'Col C
    sht.Cells(newrow, 4) = Me.ComboBox1     'sub Type Col D
    sht.Cells(newrow, 5) = Me.TxtCaseID     'Col E
    sht.Cells(newrow, 6) = Me.TxtEETime     'Col F
    '---------------------------------------------
    sht.Cells(newrow, 7) = Me.cmbClientName  'Col G
    sht.Cells(newrow, 8) = Me.cmbTaskName    'Col H
    sht.Cells(newrow, 9) = Me.cmbTaskStatus  'Col I
    sht.Cells(newrow, 10) = Me.txtcomm       'Col K
    sht.Cells(newrow, 11) = Me.LblUname      'Col L
    Workbooks("Timesheet.xlsm").Save
Else
    Dim ShName As String
    Worksheets("Sheet1").Visible = True
    Sheets("Sheet1").Select
    With ActiveSheet
    ShName = .Name & "Copy"
    .Copy After:=Sheets(Worksheets.Count)
    End With
    'Sheets(Worksheets.Count).Name = ShName
    ActiveWorkbook.Worksheets(ShName).Name = LblUname
    NewSh = ActiveSheet.Name


    Set sht = Application.Workbooks("Timesheet.xlsm").Sheets(NewSh)
    newrow = sht.Cells(Rows.Count, 1).End(xlUp).Row + 1
    sht.Cells(newrow, 1) = CDate(Me.txtstartdate)
    Closed_date = DateTime.Now
    sht.Cells(newrow, 2) = CDate(Closed_date)
    '---------------------------------------------
    sht.Cells(newrow, 3) = Me.cmbActivity   'Col C
    sht.Cells(newrow, 4) = Me.ComboBox1     'sub Type Col D
    sht.Cells(newrow, 5) = Me.TxtCaseID     'Col E
    sht.Cells(newrow, 6) = Me.TxtEETime     'Col F
    '---------------------------------------------
    sht.Cells(newrow, 7) = Me.cmbClientName  'Col G
    sht.Cells(newrow, 8) = Me.cmbTaskName    'Col H
    sht.Cells(newrow, 9) = Me.cmbTaskStatus  'Col I
    sht.Cells(newrow, 10) = Me.txtcomm       'Col K
    sht.Cells(newrow, 11) = Me.LblUname      'Col L
    Workbooks("Timesheet.xlsm").Save
    Worksheets("Sheet1").Visible = False
    Exit Sub
End If
Next x

 

[VBABEGINER]

LblUname = Label taken on userform

[COLOR=#ff0000]If Sheets(x).Name = LblUname Then[/COLOR]

In the Red color part, I want to check whether any sheets are available or not with user name which is appearing on Label.
if it is there, then no need to create copy of "Sheet1". It will paste the data with existing sheet.
Else,
create a copy of "Sheet1" worksheet and name that worksheet with user name.

 

[dmt32]

try adding the Labels Caption property

Me.LblUname.Caption

Dave

 

[VBABEGINER]

where and how..

 

[dmt32]

where and how..

where ever in your code you refer to the label & how shown in my post.

Dave

 

[VBABEGINER]

Can any one else, please provide solution on this..

 

[VBABEGINER]

This is some modified code..
Pls sugeest..

Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
End Function

Dim x As Integer
Dim y As Integer
Dim NewSh As Worksheet
Dim p As Worksheet


y = Sheets.Count


For x = 1 To y


    If Not WorksheetExists(p) Then
        Exit Sub
    Else
        If Sheets(x).Name = LblUname Then
        NewSh = ActiveSheet.Name
        Set sht = Application.Workbooks("Timesheet.xlsm").Sheets(NewSh)
        newrow = sht.Cells(Rows.Count, 1).End(xlUp).Row + 1
        sht.Cells(newrow, 1) = CDate(Me.txtstartdate)
        Closed_date = DateTime.Now
        sht.Cells(newrow, 2) = CDate(Closed_date)
        '---------------------------------------------
        sht.Cells(newrow, 3) = Me.cmbActivity   'Col C
        sht.Cells(newrow, 4) = Me.ComboBox1     'sub Type Col D
        sht.Cells(newrow, 5) = Me.TxtCaseID     'Col E
        sht.Cells(newrow, 6) = Me.TxtEETime     'Col F
        '---------------------------------------------
        sht.Cells(newrow, 7) = Me.cmbClientName  'Col G
        sht.Cells(newrow, 8) = Me.cmbTaskName    'Col H
        sht.Cells(newrow, 9) = Me.cmbTaskStatus  'Col I
        sht.Cells(newrow, 10) = Me.txtcomm       'Col K
        sht.Cells(newrow, 11) = Me.LblUname      'Col L
        Workbooks("Timesheet.xlsm").Save
        
        Else
        
        Dim ShName As String
        Worksheets("Sheet1").Visible = True
        Sheets("Sheet1").Select
        With ActiveSheet
        ShName = .Name & "Copy"
        .Copy After:=Sheets(Worksheets.Count)
        End With
        'Sheets(Worksheets.Count).Name = ShName
        ActiveWorkbook.Worksheets(ShName).Name = LblUname
        NewSh = ActiveSheet.Name
    
        Set sht = Application.Workbooks("Timesheet.xlsm").Sheets(NewSh)
        newrow = sht.Cells(Rows.Count, 1).End(xlUp).Row + 1
        sht.Cells(newrow, 1) = CDate(Me.txtstartdate)
        Closed_date = DateTime.Now
        sht.Cells(newrow, 2) = CDate(Closed_date)
        '---------------------------------------------
        sht.Cells(newrow, 3) = Me.cmbActivity   'Col C
        sht.Cells(newrow, 4) = Me.ComboBox1     'sub Type Col D
        sht.Cells(newrow, 5) = Me.TxtCaseID     'Col E
        sht.Cells(newrow, 6) = Me.TxtEETime     'Col F
        '---------------------------------------------
        sht.Cells(newrow, 7) = Me.cmbClientName  'Col G
        sht.Cells(newrow, 8) = Me.cmbTaskName    'Col H
        sht.Cells(newrow, 9) = Me.cmbTaskStatus  'Col I
        sht.Cells(newrow, 10) = Me.txtcomm       'Col K
        sht.Cells(newrow, 11) = Me.LblUname      'Col L
        Workbooks("Timesheet.xlsm").Save
        Worksheets("Sheet1").Visible = False
        Exit Function
        End If
    End If
Next x

 

[VBABEGINER]

Okk, Board members. im closing this query...as lot many confusion to me as well