copy range to separate sheet

[zpierucci]

I'm having an issue copying data to another sheet within the same workbook.

in the code provided the program works fine.

Sub dinocheck()


Dim i As Integer
Dim r2 As Integer
Dim lastrowdl, lastrowc, lastrowh, lastrowo, lcol As Long




lastrowdl = Sheets("dino list").Cells(Rows.Count, 1).End(xlUp).Row
lastrowc = Sheets("carnivore").Cells(Rows.Count, 1).End(xlUp).Row
lastrowh = Sheets("herbivore").Cells(Rows.Count, 1).End(xlUp).Row
lastrowo = Sheets("omnivore").Cells(Rows.Count, 1).End(xlUp).Row
lcol = Sheets("dino list").Cells(1, Columns.Count).End(xlToLeft).Column


    For i = 2 To lastrowdl
    
        If Sheets("dino list").Cells(i, 5).Value = "carnivore" Then
            Sheets("dino list").Range(Cells(i, 1), Cells(i, lcol)).Copy Sheets("dino list").Range(Cells(lastrowdl + 1, 1), Cells(lastrowdl + 1, lcol))
            End If
            
            
            
        Next i
        
    
    


End Sub

but anytime I try to change the copy destination to another sheet. I get an error. I did change the lastrow variable to the sheet that I was trying to copy to.

 

[DanteAmor]

Sometimes it is not necessary to have a variable for each sheet, on the same line you can get the last row of the sheet.

I guess you want to copy each concept on its respective sheet.
Try this:

Sub dinocheck()
  Dim i As Long, sh1 As Worksheet, sh2 As Worksheet, lcol As Long
  Set sh1 = Sheets("dino list")
  lcol = sh1.Cells(1, Columns.Count).End(xlToLeft).Column
  For i = 2 To sh1.Range("A" & Rows.Count).End(xlUp).Row
    Select Case sh1.Cells(i, "E").Value
      Case "carnivore": Set sh2 = Sheets("carnivore")
      Case "herbivore": Set sh2 = Sheets("herbivore")
      Case "omnivore":  Set sh2 = Sheets("omnivore")
      Case Else:        Set sh2 = Nothing
    End Select
    If Not sh2 Is Nothing Then
      sh1.Range([COLOR=#0000ff]sh1[/COLOR].Cells(i, 1), [COLOR=#0000ff]sh1[/COLOR].Cells(i, lcol)).Copy [COLOR=#0000ff]sh2[/COLOR].Range("A" & [COLOR=#0000ff]sh2.Range("A" & Rows.Count).End(xlUp).Row + 1[/COLOR])
    End If
  Next i
End Sub

 

[zpierucci]

Thanks Dante. That does work and is probably a better way to move the data. I am still curious as to what the best way would be to copy a range from one sheet to another using variables. It's weird that it will work on the sheet being copied from but not being able to copy the same data to another sheet.

 

[DanteAmor]

yes, it is possible to copy from one sheet to another, you just have to put the correct reference of the sheets, as I highlighted in blue.