How to Copy values only, except formatting.

Hopy

New Member
Joined
Aug 4, 2022
Messages
8
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
  6. 2011
  7. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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:

VBA Code:
                    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:

VBA 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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0
I'd recommend the above solution but if you really wanna copy and paste without formatting.
VBA Code:
Sub Cut_Copy()
         Range("A2").Copy 
         Range("C2").PasteSpecial xlPasteValues 'paste destination
         
          Application.CutCopyMode = False
              
End Sub
 
Upvote 0
VBA Code:
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
 
Upvote 0
I'd recommend the above solution but if you really wanna copy and paste without formatting.
VBA Code:
Sub Cut_Copy()
         Range("A2").Copy
         Range("C2").PasteSpecial xlPasteValues 'paste destination
        
          Application.CutCopyMode = False
             
End Sub
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 ;)
 
Upvote 0
In final just use .ClearFormats for target Tabs...
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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