# How to Copy values only, except formatting.



## Hopy (Tuesday at 7:00 AM)

Hello every one.
I have a code, who copied  data range from input table to another tabs based on A row vales.

code who make this copy:


```
With Sheet1.[A1].CurrentRegion
                    .Value = .Value
                         .AutoFilter 1, ar(x), 7
                         .Offset(1).Resize(.Rows.Count - 1).Copy Sheets(ar(x)).Range("A" & Rows.Count).End(3)(2)
                         .AutoFilter
                         Application.CutCopyMode = False
                    End With
```

But this code  keep input table cell formatting, how to modify this code to copy only cell values, and except cell formatting?

Whole code:


```
Set DataRange = Sheet1.Range("X1:X" & Sheet1.Cells(1, 23).Value)
  For Each Cell In DataRange.Cells
    MyString = MyString & ";|;" & Cell.Value
  Next Cell
  MyString = Right(MyString, Len(MyString) - 3)
  ar = Split(MyString, ";|;")
  For x = LBound(ar) To UBound(ar)

           
            
                    With Sheet1.[A1].CurrentRegion
                    .Value = .Value
                         .AutoFilter 1, ar(x), 7
                         .Offset(1).Resize(.Rows.Count - 1).Copy Sheets(ar(x)).Range("A" & Rows.Count).End(3)(2)
                         .AutoFilter
                         Application.CutCopyMode = False
                    End With
          
Application.ScreenUpdating = False
  Next x
```


----------



## Herakles (Tuesday at 7:11 AM)

You do not have to copy the values.

For example below, the values in the B3:B8 range will be applied to the E3:E8 range.

Range("E3:E8").Value = Range("B3:B8").Value


----------



## shinigamilight (Tuesday at 7:17 AM)

I'd recommend the above solution but if you really wanna copy and paste without formatting.

```
Sub Cut_Copy()
         Range("A2").Copy 
         Range("C2").PasteSpecial xlPasteValues 'paste destination
         
          Application.CutCopyMode = False
              
End Sub
```


----------



## cspengel (Tuesday at 7:24 AM)

```
Set DataRange = Sheet1.Range("X1:X" & Sheet1.Cells(1, 23).Value)
MyString = ""
For Each Cell In DataRange.Cells
    MyString = MyString & ";|;" & Cell.Value
Next Cell
MyString = Right(MyString, Len(MyString) - 3)
ar = Split(MyString, ";|;")
Application.ScreenUpdating = False
For x = LBound(ar) To UBound(ar)
    With Sheet1.[A1].CurrentRegion
        .Value = .Value
        .AutoFilter 1, ar(x), 7
        .Offset(1).Resize(.Rows.Count - 1).Value = Sheets(ar(x)).Range("A" & Rows.Count).End(3)(2).Value
        .AutoFilter
        Application.CutCopyMode = False
    End With
Next x
Application.ScreenUpdating = True
```


----------



## Fluff (Tuesday at 7:27 AM)

@cspengel that is copying to Sheet1, not from Sheet1.


----------



## Hopy (Tuesday at 7:59 AM)

shinigamilight said:


> I'd recommend the above solution but if you really wanna copy and paste without formatting.
> 
> ```
> Sub Cut_Copy()
> ...


My range isn't static and it set be Array arg. So PasteSpecial xlPasteValues, doesnt works, or my VBA skill hide this solution from me


----------



## Hopy (Tuesday at 9:08 AM)

In final just use .ClearFormats for target Tabs...


----------

