Change VBA to paste as values

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
442
Office Version
  1. 365
Platform
  1. Windows
Guys, how do i change the below to paste as values only?

Code:
Sub Bethan_New_Calls_Service_and_MOT()    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim i As Long, j As Long
    Set sh1 = Sheets("Audi Service & MOT New Calls")
    Set sh2 = Sheets("All Data")
    
    'Filter New Call
    Sheets("Audi Service & MOT New Calls").Select
    ActiveSheet.ListObjects("Audi_New_SANDM").Range.AutoFilter Field:=4, _
        Criteria1:="New Calls"
    'Filter the relevant Contcode
    ActiveSheet.ListObjects("Audi_New_SANDM").Range.AutoFilter Field:=13, _
        Criteria1:=Array("Kerridge Service", "Kerridge Service & MOT", "Polk Service", _
        "Polk Service & MOT"), Operator:=xlFilterValues
    'Filter by person
    ActiveSheet.ListObjects("Audi_New_SANDM").Range.AutoFilter Field:=16, _
        Criteria1:="Bethan M"
    
    i = 1
    For j = 1 To Rows.Count
        If sh1.Cells(j, 1).EntireRow.Hidden = False Then
            sh1.Cells(j, 1).EntireRow.Copy sh2.Cells(i, 1)
            i = i + 1
            If i = Range("Allocate!e3").Value + 2 Then Exit Sub
        End If
    Next j
End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Change this:

Code:
sh1.Cells(j, 1).EntireRow.Copy sh2.Cells(i, 1)

to this:

Code:
sh1.Cells(j, 1).EntireRow.Copy
sh2.Cells(i, 1).PasteSpecial xlPasteValues
 
Upvote 0
Thanks for that, works great but runs really slow. Anyway around that?
 
Upvote 0
You could just skip the loop and copy the table:

Code:
    ActiveSheet.ListObjects("Audi_New_SANDM").Range.specialcells(xlcelltypevisible).copy
sh2.Cells(1, 1).PasteSpecial xlPasteValues
application.cutcopymode = false
 
Upvote 0
Thank you for that much appreciated. Just out of interest I will eventually have 10 (or more depending on the amount of names) of the same code above for each name in Field 16. So i will run 10 or more separate macros. For each name I change
Code:
If i = Range("Allocate!e3").Value + 2 Then Exit Sub
to a new cell as different names will have a different value, ie, copy 25 for Bethan, 30 for Angela etc etc and then Paste all of the data to one sheet below each other. Is there a quicker way around this . Hope this makes sense
 
Upvote 0
If column A on sh2 always has data in it for each row, you could just use:

Code:
sh2.Cells(rows.count, 1).End(xlUP).Offset(1).PasteSpecial xlPasteValues

to just paste to the next blank row.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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
Back
Top