How to copy information from one sheet into another sheet?-CodePudding

I am trying to copy information from one sheet into another sheet. To do so I wrote a code that goes down the column until it finds an empty cell and checks if the cell contains the word dail. Then it's supposed to copy the word daily and the words from it's surrounding cells into the new sheet created called task list. When I run the code it does add the new sheet but for some reason it does not copy the info into the new sheet.

Private Sub PopulateTaskList()

Dim exists As Boolean
Dim i As Integer
   
    'Create new sheet named
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = "Task List" Then Exit Sub
    Next
    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("Taks List").Rows("2:2").EntireRow.Insert
            Worksheets("Task List").Range("B2").Paste
            ActiveCell.Offset(0, 1).Select
            Worksheets("Task List").Range("D2").Paste
            ActiveCell.Offset(0, -2).Select
            Worksheets("Task List").Range("C2").Paste
            ActiveCell.Offset(0, -1).Select
            Worksheets("Task List").Range("A2").Paste
        End If
    Loop
End Sub

CodePudding user response：

Please, try the next adapted code. It does not select/activate anything, since this only consumes Excel resources not bringing any benefit (potential problems creator), places the cell to be copied after row insertion (otherwise, the copied value would be copied in all inserted row) and fully qualify the ranges to be copied. Other adaptations commented on the code lines:

Private Sub PopulateTaskList()
 Dim wMS As Worksheet, wsTL As Worksheet, rngC As Range
 Dim boolExists As Boolean, i As Long
   
    'Create new sheet named
    For i = 1 To Worksheets.count 'check if the sheet exists
        If Worksheets(i).name = "Task List" Then
            wsTL = Worksheets(i)
            boolExists = True
            Exit Sub
        End If
    Next i
    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")
    Do Until rngC.Value = ""
        If rngC.Value = "Daily" Then
            wsTL.rows("2:2").EntireRow.Insert
            rngC.Copy wsTL.Range("B2") 'if you place the range in clipboard, it will be copied during the row insertion...
            
            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
        Set rngC = rngC.Offset(1) 'set the range as the next cell down
    Loop
End Sub