VBA copy & paste without formatting

[JAQ1983]

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:

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

 

[yky]

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.

 

[JAQ1983]

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

 

[B4andafter]

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>

 

[yky]

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.

    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:="<>"

 

[JAQ1983]

Thank you so much.

This seems to have fixed my problem.

Many thanks, Jo