Copy range value, format and formulas

Apple08

Active Member
Joined
Nov 1, 2014
Messages
404
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.

VBA Code:
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,585
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Apple08

Active Member
Joined
Nov 1, 2014
Messages
404
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,585
Office Version
  1. 365
Platform
  1. Windows
What formulae?
 

Apple08

Active Member
Joined
Nov 1, 2014
Messages
404

ADVERTISEMENT

Please see the code below:

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,585
Office Version
  1. 365
Platform
  1. Windows
If you're getting values, either there is some other code at work, or there are nor formulae to copy.
 

Apple08

Active Member
Joined
Nov 1, 2014
Messages
404

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,585
Office Version
  1. 365
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,585
Office Version
  1. 365
Platform
  1. Windows
Ok, you have a number of circular references, which maybe causing the problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,619
Messages
5,659,903
Members
418,536
Latest member
Tezzies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top