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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

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...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,190
Messages
5,594,761
Members
413,931
Latest member
acrato

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