Copy range till last row from sheet x to sheet y.

[calvinle]

Hi,

I am currently using this macro to copy from 1 sheet to another sheet, but what can I do if I want the Sheet 2 to copy only all data value starting Column B1 till last data on last row. Not only data on B1, but starting from B1 to end of column data and row data..then paste into another sheet 3 on A1 as value.

  Application.ScreenUpdating = False
  Worksheets("Sheet2").Range("B1:Y34").Value = Worksheets("Sheet3").Range("A1").Value
  Application.ScreenUpdating = True

Please hope some help.

Thank.

[:) Sixthsense :)]

Try this way...

Application.ScreenUpdating = False
    Worksheets("Sheet2").Range("B1").CurrentRegion.Copy Worksheets("Sheet3").Range("A1")
Application.ScreenUpdating = True

[millz]

Hi Sixthsense, that would also include row 1 and column A if there are any data.

I suggest this way:

Application.ScreenUpdating = False
If Application.CountA(rows(1), Columns(1)) = 0 Then
    Worksheets("Sheet2").Range("B1").CurrentRegion.Copy Worksheets("Sheet3").Range("A1")
Else
    With Worksheets("Sheet2").Range("A1").CurrentRegion
        .Offset(1, 1).Resize(.rows.Count - 1, .Columns.Count - 1).Copy Worksheets("Sheet3").Range("A1")
    End With
End If
Application.ScreenUpdating = True

[calvinle]

This will copy the data with formula, but I want the final data on Sheet 3 to be only paste as value...

[millz]

Then:

Application.ScreenUpdating = False
If Application.CountA(rows(1), Columns(1)) = 0 Then
    Worksheets("Sheet2").Range("B1").CurrentRegion.Copy
    Worksheets("Sheet3").Range("A1").PasteSpecial xlPasteValues
Else
    With Worksheets("Sheet2").Range("A1").CurrentRegion
        .Offset(1, 1).Resize(.rows.Count - 1, .Columns.Count - 1).Copy
        Worksheets("Sheet3").Range("A1").PasteSpecial xlPasteValues
    End With
End If
Application.ScreenUpdating = True

[calvinle]

This way does not copy from B1, but it copy from B2?? The header on B1 was not copied to the Sheet 3. or nothing on column 1 was copied to Sheet 3.

[millz]

Oh, my bad, kept thinking it was row 2 onwards.

Application.ScreenUpdating = False
If Application.CountA(Columns(1)) = 0 Then
    Worksheets("Sheet2").Range("B1").CurrentRegion.Copy
    Worksheets("Sheet3").Range("A1").PasteSpecial xlPasteValues
Else
    With Worksheets("Sheet2").Range("A1").CurrentRegion
        .Offset(, 1).Resize(, .Columns.Count - 1).Copy
        Worksheets("Sheet3").Range("A1").PasteSpecial xlPasteValues
    End With
End If
Application.ScreenUpdating = True