Copy range value, format and formulas

[Apple08]

Hi All

I want to copy and paste a range's value, format and formulas, I have a macro below and please could you let me know how to edit it to make all of the values/format/formulas be included? Thanks.

Private Sub Copy()

Dim Last_Row As Long
Sheets("Master").Select
Last_Row = Range("A" & Rows.Count).End(xlUp).Row

   
Dim LR As Long
    LR = Sheets("Copy").Range("A" & Rows.Count).End(xlUp).Row
    
    Sheets("Copy").Range("A2:AC" & LR).Copy
    Sheets("Master").Range("A" & Last_Row + 1).PasteSpecial


End Sub

 

[Fluff]

How about

    Sheets("Copy").Range("A2:AC" & LR).Copy    Sheets("Master").Range("A" & Last_Row + 1)

 

[Apple08]

Thanks Fluff, it works perfectly!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Apple08]

Hi Fluff

I have another macro below and please could you help to edit it to paste in all values/format/formulas? Many thanks.

Sub I_PasteData_Master()

Dim Del As Variant
Del = Array("STEM", "FASS", "WELS", "PVC-S", "FBL") ' Modify Sheet names if needed
Dim lastrow As Long
Dim ans As Long
ans = UBound(Del)
Dim c As Long
Dim counter As Long
counter = 0
c = "1" ' Column Number Modify this to your need
lastrow = Cells(Rows.Count, c).End(xlUp).Row

On Error Resume Next
    Sheets("SUP Tracker").ShowAllData
  On Error GoTo 0

With Sheets("SUP Tracker").Cells(1, c).Resize(lastrow)

For i = 0 To ans
    .AutoFilter 1, Del(i), Operator:=xlFilterValues
    counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(Del(i)).Cells(2, 1)
counter = 0
Next
.AutoFilter
End With
End Sub

 

[Fluff]

Try

With Sheets("SUP Tracker")
For i = 0 To ans
    .Cells(1, C).Resize(LastRow).AutoFilter 1, Del(i), Operator:=xlFilterValues
    counter = .Columns(C).SpecialCells(xlCellTypeVisible).Count
    If counter > 1 Then .AutoFilter.Range.Offset(1).Resize(LastRow - 1).EntireRow.Copy Sheets(Del(i)).Cells(2, 1)
counter = 0
Next
.AutoFilter
End With

 

[Apple08]

Thanks Fluff, however the macro stopped at .AutoFilter while running in the middle, please could you help.

i wonder it could be something to do with this:

On Error Resume Next
    Sheets("SUP Tracker").ShowAllData
  On Error GoTo 0

 

[Fluff]

What is the error message & number & what line is highlighted?

 

[Apple08]

Run-time error '438'
Object doesn't support this property or method

.AutoFilter is highlighted

 

[Fluff]

Try changing it to

.AutoFilterMode = False