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

 

[Apple08]

It works perfectly! Thousand thanks Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Apple08]

Hi Fluff, I run the report today but noticed the formulas haven't been pasted in as expected.... please are you able to help? Thanks.

 

[Fluff]

What formulae?

 

[Apple08]

Please see the code below:

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")
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
.AutoFilterMode = False
End With


End Sub

The macro only pasted in the values but not the formulas from SUP Tracker worksheet to other tabs.

 

[Fluff]

If you're getting values, either there is some other code at work, or there are nor formulae to copy.

 

[Apple08]

It' very strange, sometimes it works but sometimes it doesn't... Do you mind if I send you the Excel file for you to have a look? Thanks.

 

[Fluff]

Upload it to a share site such as OneDrive, Google Drive, dropbox. Mark it for sharing & then paste the link you are given to the thread.

 

[Apple08]

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Dropbox - File Deleted - Simplify your life

www.dropbox.com

I hope you can access the Dropbox links above. I used the last macro I_PasteData_Master, however the formulas are not included in the paste. Thanks for the help Fluff.

 

[Fluff]

Ok, you have a number of circular references, which maybe causing the problem.