0

I am trying to create a list in order from a small database. Basically, I am trying to check in and tell the program to go from C3 in the MSS sheet until it finds an empty cell. While it goes down I wrote an if statement that is supposed to copy the info in C3 and its surroundings into another sheet that the program is supposed to create named task list. I want to do this for daily, then weekly, and so on. So far I have tried to do the daily but it doesn't seem to work.

Here is the code.

Private Sub TaskListContent()
'Creates new worksheet to make a task list
Const wsName As String = "MSS"

Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Task List"

Worksheets("MSS").Activate
Range("C3").Select
Do Until ActiveCell.Value = ""
    If ActiveCell.Value = "Daily" Then
        ActiveCell.Copy
        ActiveCell.FormulaR1C1 = "Daily"
        Worksheets("wsName_tskl").Rows("2:2").EntireRow.Insert
        Worksheets("wsName_tskl").Range("B2").Paste
        ActiveCell.Offset(0, 1).Select
        Worksheets("wsName_tskl").Range("D2").Paste
        ActiveCell.Offset(0, -2).Select
        Worksheets("wsName_tskl").Range("C2").Paste
        ActiveCell.Offset(0, -1).Select
        Worksheets("wsName_tskl").Range("A2").Paste
    End If
Loop

End Sub

I can't seem to create the task list sheet and also can't seem to reference the MMS sheet. enter image description here

Daniel
  • 4,792
  • 2
  • 7
  • 20
David Rojas
  • 39
  • 5
  • You add the new worksheet and call it "Task List". Then in the following code you referencing `Worksheets("wsName_tskl")`. If you are trying to work with the new worksheet then this should be `Worksheets("Task List")` – Daniel Sep 15 '22 at 03:17
  • Thanks, I fixed that but for some reason I keep getting an error that says that the name is already taken and I can't figure out what it is referring to. – David Rojas Sep 15 '22 at 04:05
  • Yes, you will get that error when you try change a name of a worksheet to an already existing worksheet's name which you've got already because you've previously ran the script. Have a look [here](https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists) for ways to check if the worksheet exists before trying to create another – Daniel Sep 15 '22 at 04:22
  • I'd first check that the sheet doesn't already exist - check Tim Williams answer on [Test or check if sheet exists](https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists) and then use this code to get a reference to the sheet: `Dim wrkSht As Worksheet: Set wrkSht = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)): wrkSht.Name = "Task List"`. This code can be shortened using a `With...End With` block. – Darren Bartrup-Cook Sep 15 '22 at 08:01

0 Answers0