How to copy information from one sheet into another sheet?

Question

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

Fully qualify your objects instead of relying on what is active - https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — braX, Sep 16 '22 at 02:38
Your entire loop consists of the `If` based on a cell with the value "Daily". Nothing in the loop is actually changing cell to look in. — Christofer Weber, Sep 16 '22 at 05:46
Your code is a little strange. Since it search for the word "Daily", why using `ActiveCell.FormulaR1C1 = "Daily"` for a cell having already this value? Only to empty Clipboard and not being able to copy, anymore? Then your iteration does not do anything. Since the code does not **activate** another cell, it only process the activated one ("C3"). Then, what do expect from `ActiveCell.Offset(0, 1).Select`? The new `ActiveCell` is the one where the values has been pasted (if something in clipboard)... Finally, do you want/need **copying the cells format**? — FaneDuru, Sep 16 '22 at 07:11
Does this answer your question? [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Dominique, Sep 16 '22 at 11:03

score 0 · Accepted Answer · answered Sep 16 '22 at 07:54

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

Hi, thanks a lot for the help. Your code definitely makes more sense than mine. The only issue I am having with it is that it gives me a run time error '91 which tells me it happens when an object variable is not set. I know you set them in the if statements but for some reason it won't allow that I guess. — David Rojas, Sep 17 '22 at 19:03
@David Rojas I missed it for the case when the sheet already exists (`Set wsTL = Worksheets(i)`). Please, test the updated code and send some feedback. — FaneDuru, Sep 17 '22 at 19:21

How to copy information from one sheet into another sheet?

1 Answers1