red4rojas
10-29-2022, 04:10 PM
Hi, I have this idea for this program that goes from down column C in a sheet called MSS and copies the values next to daily but only once and avoids repeating site values. So for example if in sheet MSS under Site Columns there are two cells that say tools I only want it to copy it once to the sheet Task List under column C starting at C2.
I have the following code but it dosen't seem to work.
Private Sub PopulateTaskList()
Dim wMS As Worksheet, wsTL As Worksheet, rngC As Range, rngTL As Range
Dim boolExists As Boolean, i As Long
Dim verify As String
'Check to see if sheet named Task List exists
For i = 1 To Worksheets.Count 'check if the sheet exists
If Worksheets(i).Name = "Task List" Then
Set wsTL = Worksheets(i)
boolExists = True
Exit Sub '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
End If
Next i
'Add sheet named Taks List if it dosen't exist
If Not boolExists Then
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Task List"
Set wsTL = Worksheets("Task List")
End If
Set wMS = Worksheets("MSS")
Set rngC = wMS.Range("C3")
Set rngTL = wsTL.Range("C2")
Do Until rngC.Value = ""
If rngC.Value = "Daily" Then
verify = rngC.Offset(0, -2).Value
If WorksheetFunction.CountIf(rngTL, verify) Then
wsTL.Rows("2:2").EntireRow.Insert
rngC.Copy wsTL.Range("B2")
rngC.Offset(0, 1).Copy wsTL.Range("D2")
rngC.Offset(0, -2).Copy wsTL.Range("C2")
rngC.Offset(0, -1).Copy wsTL.Range("A2")
End If
End If
Set rngC = rngC.Offset(1) 'set the range as the next cell down11
Loop
End Sub
30294
30295
I have the following code but it dosen't seem to work.
Private Sub PopulateTaskList()
Dim wMS As Worksheet, wsTL As Worksheet, rngC As Range, rngTL As Range
Dim boolExists As Boolean, i As Long
Dim verify As String
'Check to see if sheet named Task List exists
For i = 1 To Worksheets.Count 'check if the sheet exists
If Worksheets(i).Name = "Task List" Then
Set wsTL = Worksheets(i)
boolExists = True
Exit Sub '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
End If
Next i
'Add sheet named Taks List if it dosen't exist
If Not boolExists Then
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Task List"
Set wsTL = Worksheets("Task List")
End If
Set wMS = Worksheets("MSS")
Set rngC = wMS.Range("C3")
Set rngTL = wsTL.Range("C2")
Do Until rngC.Value = ""
If rngC.Value = "Daily" Then
verify = rngC.Offset(0, -2).Value
If WorksheetFunction.CountIf(rngTL, verify) Then
wsTL.Rows("2:2").EntireRow.Insert
rngC.Copy wsTL.Range("B2")
rngC.Offset(0, 1).Copy wsTL.Range("D2")
rngC.Offset(0, -2).Copy wsTL.Range("C2")
rngC.Offset(0, -1).Copy wsTL.Range("A2")
End If
End If
Set rngC = rngC.Offset(1) 'set the range as the next cell down11
Loop
End Sub
30294
30295