VBA copy & paste without formatting

JAQ1983

New Member
Joined
Aug 16, 2018
Messages
7
Hi All,

I have a piece of code which works well, but I am unsure how to amend it...

The code transfers data based on a criteria to the relevant worksheet. I need to amend it so that it doesn't remove any of the formatting that is present on the entire destination sheet ("RM DOM") and simply pastes the values. I have conditional formatting, formula and data validation from columns AB to AS which I need to keep. At the minute this code removes everything each time it is run. Is this possible?

Here's my code:
Code:
Private Sub RM_DOM_Click()

    Sheets("RM DOM").Unprotect "Minors"


    Dim x           As Long
    Dim LR          As Long
    Dim LC          As Long
    Dim msg         As String
    Dim arr()       As Variant
    Dim w           As Worksheet: Set w = Sheets("Minor Sales")
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationAutomatic
        
    With w
        LC = .Range("AA1").Column '27
        LR = .Cells(.Rows.Count, 2).End(xlUp).Row
        For x = 3 To LR
            msg = LCase$(.Cells(x, 2).Value & .Cells(x, 19).Value)
            If msg = "domyes" Then
                arr = .Cells(x, 1).Resize(, LC).Value
                Sheets("RM " & .Cells(x, 2).Value).Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, LC).Value = arr
            End If
        Next x
    End With
    
           
    Set w = Nothing
    msg = vbNullString
    Erase arr
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
                  
    Sheets("RM DOM").Visible = True
    Sheets("RM DOM").Range("A:AS").RemoveDuplicates Columns:=Array(3)
    Sheets("RM DOM").Range("$A$2:$AS$9970").AutoFilter Field:=27
    Sheets("RM DOM").Range("$A$2:$AS$9970").AutoFilter Field:=27, Criteria1:="<>"
    Sheets("RM DOM").Protect "Minors", True, True
    Sheets("RM DOM").Select


End Sub

Thanks, Jo
 

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.

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,880
Office Version
  1. 2010
Platform
  1. Windows
Formula, of course, can't be kept.

I did some tests and these are my findings. Conditional formatting should stay. Data validation rule should stay but you can actually copy value outside the validation rule to the cell and Excel will not complain. Excel will complain if you enter the value manually. I set a rule of greater than zero and manually entered -1. Excel did not accept it but accepted it without complaint when done by code.
 
Upvote 0

JAQ1983

New Member
Joined
Aug 16, 2018
Messages
7
Hi, I'm not sure I explained myself properly.

I don't need to copy formula, formatting etc in my code.
The code I have copies data and pastes it in to the destination sheet through columns A to AA. My formula, formatting and data validation is in columns AB to AS of this destination sheet. At the minute when I run the code it removes all the original data & formatting on the entire row.

All I want to do is copy and paste the data in to columns A to AA and keep all other information outside of this area in the sheet.

I'm sure this must be possible, I just can't understand how to amend the original code to do this?!

Many thanks, Jo
 
Upvote 0

B4andafter

New Member
Joined
Aug 9, 2017
Messages
30
Office Version
  1. 365
Platform
  1. Windows
I've been busy and not able to test your code but let's see if this will work- where you have this code:


If msg = "domyes" Then
arr = .Cells(x, 1).Resize(, LC).Value
Sheets("RM " & .Cells(x, 2).Value).Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, LC).Value = arr

'Replace this section with this:


If msg = "domyes" Then
.Cells(x, 1).Resize(, LC).Copy
Sheets("RM " & .Cells(x, 2).Value).Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, LC).PasteSpecial xlPasteValues</pre>
 
Upvote 0

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,880
Office Version
  1. 2010
Platform
  1. Windows
The only codes that deal with cells after AA are the following three lines. Comment out these three lines or replace AS with AA and see what happens.

Code:
    Sheets("RM DOM").Range("A:AS").RemoveDuplicates Columns:=Array(3)
    Sheets("RM DOM").Range("$A$2:$AS$9970").AutoFilter Field:=27
    Sheets("RM DOM").Range("$A$2:$AS$9970").AutoFilter Field:=27, Criteria1:="<>"
 
Last edited:
Upvote 0

Forum statistics

Threads
1,187,109
Messages
5,961,638
Members
438,557
Latest member
Jadatutor

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