Copy and paste columns without header to another sheet in same file

[Ananthak275]

Need to copy columns starting from A3 till last row of data from Sheet1 to Sheet2.

• the last row of the data changes every time. so it could be A3:A27 or A3:A9
• On Sheet2 (the destination sheet name) the cell should be A2

Could this also be a loop so it does it from column A & B?

 

[tj4242]

This copies from the source row (srcrow) starting with A3 to the last used row (LastRow) on Sheet1 to B2 on Sheet2. Is this what you were looking for?

Sub movesheet()
    Dim LastRow As Long
    Dim destrow As Long
        
    destrow = 2
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For srcrow = 3 To LastRow            'start at row 3
        Worksheets("Sheet2").Range("B" & destrow) = Worksheets("Sheet1").Range("A" & srcrow)
        destrow = destrow + 1
    Next
End Sub

 

[tj4242]

I think I misunderstood something. I read that is was from column A to column B. I read it again and you want to copy both column A and B to Sheet2

Sub movesheet()
    Dim LastRow As Long
    Dim destrow As Long
        
    destrow = 2
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For srcrow = 3 To LastRow            'start at row 3
        Worksheets("Sheet2").Range("A" & destrow) = Worksheets("Sheet1").Range("A" & srcrow)
        Worksheets("Sheet2").Range("B" & destrow) = Worksheets("Sheet1").Range("B" & srcrow)
        destrow = destrow + 1
    Next
End Sub

 

[Ananthak275]

I think I misunderstood something. I read that is was from column A to column B. I read it again and you want to copy both column A and B to Sheet2

Sub movesheet()
    Dim LastRow As Long
    Dim destrow As Long
       
    destrow = 2
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
   
    For srcrow = 3 To LastRow            'start at row 3
        Worksheets("Sheet2").Range("A" & destrow) = Worksheets("Sheet1").Range("A" & srcrow)
        Worksheets("Sheet2").Range("B" & destrow) = Worksheets("Sheet1").Range("B" & srcrow)
        destrow = destrow + 1
    Next
End Sub

I'm getting the error Compile error: Variable not defined for srcrow. There is another macro in this module

 

[tj4242]

You can add a "dim srcrow as long"

Sub movesheet()
    Dim LastRow As Long
    Dim destrow As Long
    Dim srcrow As Long
        
    destrow = 2
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For srcrow = 3 To LastRow            'start at row 3
        Worksheets("Sheet2").Range("A" & destrow) = Worksheets("Sheet1").Range("A" & srcrow)
        Worksheets("Sheet2").Range("B" & destrow) = Worksheets("Sheet1").Range("B" & srcrow)
        destrow = destrow + 1
    Next
End Sub