Copy paste special values macro

dsalmons

New Member
Joined
Sep 23, 2013
Messages
37
Hi All,

I'm after a bit of help!

I have an excel speradsheet with columns A to AB populated with a mix of data and lookups. what I would like to do is have a macro that would look at cell AB1 and if an "X" is populated it would copy the row and paste it as values then look for the next "X" in column AB and repeat the paste special.

By doing this I'm hoping to reduce the overall file size.

Thanks for looking

Daron
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

elmer007

Active Member
Joined
Aug 29, 2014
Messages
299
I believe the following macro will get you to what you're after, but let me know if it doesn't. Also, PLEASE NOTE that you cannot click undo after running the code, so you may want to make a backup file before you start testing.

Click in cell AB1 before running this macro. Also, this will check through 10,000 rows for X's. If you need more, then change the 10000 in the code to however many rows you need (or let me know some other condition that could indicate when to stop running).

Also, capitalization matters for the X's. This finds capital X only. A better solution may be to tag each row in column AB with a 1 instead of an X (if you do, then change the "X" to 1 in the code).

Code:
If ActiveCell.Column <> 28 Then
    MsgBox ("Select cell AB1 to begin this macro.")
    Exit Sub
Else
    If ActiveCell.Row <> 1 Then
        MsgBox ("Select cell AB1 to begin this macro.")
    Else
        Do Until ActiveCell.Row = 10000
        If ActiveCell.Value = "X" Then
            ActiveCell.EntireRow.Select
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Application.CutCopyMode = False
            ActiveCell.Offset(0, 28 - ActiveCell.Column).Select
        End If
        ActiveCell.Offset(1, 0).Select
        Loop
    End If
End If
End Sub

If this code is too slow, it can be sped up. I'm just checking to see if it does what you're looking for at first.
 
Last edited:

elmer007

Active Member
Joined
Aug 29, 2014
Messages
299
Just noticed that I didn't copy the first line of code:
Code:
Sub Macro1()

Goes one line above the "If ActiveCell.Column <> 28 Then" line...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,156
Messages
5,835,707
Members
430,381
Latest member
tntcute

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