VBA - editing code to paste without table formatting

[yits05]

Hello,

I am new to VBA and would appreciate any help!

I have the below code, which does a good job of copying a range and pasting it in the furthest down blank cell in a column. However, I would like it to paste it without any formatting (including table formatting). Appreciate any help.

Sheets("Data2").Select
    On Error Resume Next
    Set mylastcell = Cells(1, 1).SpecialCells(xlLastCell)
    mylastcelladd = Cells(mylastcell.Row, mylastcell.Column).Address
    myrange = "A1:" & mylastcelladd
    Range(myrange).Select
    Range(Selection, Selection.End(xlDown)).Copy Destination:=Sheets("Sheet1").Range("P" & Rows.Count).End(xlUp).Offset(1)

 

[Lian]

Hello,

I am new to VBA and would appreciate any help!

I have the below code, which does a good job of copying a range and pasting it in the furthest down blank cell in a column. However, I would like it to paste it without any formatting (including table formatting). Appreciate any help.

Sheets("Data2").Select
    On Error Resume Next
    Set mylastcell = Cells(1, 1).SpecialCells(xlLastCell)
    mylastcelladd = Cells(mylastcell.Row, mylastcell.Column).Address
    myrange = "A1:" & mylastcelladd
    Range(myrange).Select
    Range(Selection, Selection.End(xlDown)).Copy Destination:=Sheets("Sheet1").Range("P" & Rows.Count).End(xlUp).Offset(1)

Sheets("Data2").Select
    On Error Resume Next
    Set mylastcell = Cells(1, 1).SpecialCells(xlLastCell)
    mylastcelladd = Cells(mylastcell.Row, mylastcell.Column).Address

    myrange = "A1:" & mylastcelladd
    
    Range(myrange).Copy
    Sheets("Sheet1").Range("P" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False