Adjust macro to copy values not formulas

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
358
Office Version
  1. 2019
Platform
  1. Windows
I have the following macro that copies data from current worksheet to worksheet called DATA MONTHLYS when cell nx1 = xxxxxx.
But currently it copies formulas, is there a way to make it copy the values made via the formulas.
Thanks

VBA Code:
Sub DataMonthlys()
   Dim i As Long
  
   For i = 1 To 60
      With Sheets(CStr(i))
         If LCase(.Range("nx1").Value) = "yes" Then
            .Range("tn4:tp75").Copy Workbooks("DATA MONTHLYS").Sheets("SHEET1").Range("a" & Rows.Count).End(xlUp).Offset(1)
            .Range("uv4:uw289").Copy Workbooks("DATA MONTHLYS").Sheets("SHEET1").Range("d" & Rows.Count).End(xlUp).Offset(1)
         ElseIf LCase(.Range("nx1").Value) = "no" Then
            .Range("tn4:tp75").Copy Workbooks("DATA MONTHLYS").Sheets("SHEET2").Range("a" & Rows.Count).End(xlUp).Offset(1)
            .Range("uv4:uw289").Copy Workbooks("DATA MONTHLYS").Sheets("SHEET2").Range("d" & Rows.Count).End(xlUp).Offset(1)
         End If
      End With
   Next i
End Sub
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

JB2020

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
You can split it into two rows as below:

VBA Code:
.Range("tn4:tp75").Copy
Workbooks("DATA MONTHLYS").Sheets("SHEET1").Range("a" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlpastevalues
 
Solution

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,461
Or, alternatively, try replacing...

VBA Code:
.Range("tn4:tp75").Copy Workbooks("DATA MONTHLYS").Sheets("SHEET1").Range("a" & Rows.Count).End(xlUp).Offset(1)

with

VBA Code:
With .Range("tn4:tp75")
    Workbooks("DATA MONTHLYS").Sheets("SHEET1").Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(.Rows.Count, .Columns.Count).Value = .Value
End With


And, the same thing for the rest.

Hope this helps!
 

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
358
Office Version
  1. 2019
Platform
  1. Windows
Thanks for both suggestions, all done
 

Watch MrExcel Video

Forum statistics

Threads
1,127,531
Messages
5,625,369
Members
416,098
Latest member
jpmiralrio

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