• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Code - Copy & Paste Special column if Cell contains specific Text

APM

New Member
Hi Excel Gurus,
I am seeking help with a VBA Code. I am learning VBA, but I hit a roadblock.
I would like to copy and paste special columns containing a specific Text String at the top (see picture). I would like to do the copy and paste action on the same sheet and I would like the code to scan the entire sheet for the Text string "Paste". As you see in the picture I have the Text String "Paste" in row 1 column D. I would like to run a code that Copy and Paste special the column where "Paste" Text String is. The Text String will be in row 1 and column D through Z or cell D1 to Z1.
In advance, thank you so much for your help!

69412
 

AlanSidman

Well-Known Member
Where would you like the Data Pasted? You indicate Paste Special. Do you mean Paste Special Values or some other Paste Special. You really need to tell us the whole story so that we can help you as we are not mind readers. Are all rows in the column (Row 1 through the last row) to be copied?
 

APM

New Member
Where would you like the Data Pasted? You indicate Paste Special. Do you mean Paste Special Values or some other Paste Special. You really need to tell us the whole story so that we can help you as we are not mind readers. Are all rows in the column (Row 1 through the last row) to be copied?
I would like the data in column D so row 1 to end of the rows with a value to be "copy and paste values" because of the Text String "Paste" in cell (D,1). I hope it makes sense.
In other words look row D1 through Z1 if "Paste" is there and then copy and paste values the corresponding column.
Thanks for the help Alan!
 
Last edited:

AlanSidman

Well-Known Member
The following is untested as you did not provide a worksheet to test. We are not able to manipulate data in pictures. So if you have any issues with my code, then I suggest you upload a sample workbook that can be tested.

Code:
Option Explicit

Sub APM()
    Dim i As Long
    For i = 4 To 26
        If Cells(1, i) = "Paste" Then
            Cells(1, i).EntireColumn.Copy
            Cells(1, i).PasteSpecial xlPasteValues
            Exit For
        End If
    Next i
End Sub
 

APM

New Member
The following is untested as you did not provide a worksheet to test. We are not able to manipulate data in pictures. So if you have any issues with my code, then I suggest you upload a sample workbook that can be tested.

Code:
Option Explicit

Sub APM()
    Dim i As Long
    For i = 4 To 26
        If Cells(1, i) = "Paste" Then
            Cells(1, i).EntireColumn.Copy
            Cells(1, i).PasteSpecial xlPasteValues
            Exit For
        End If
    Next i
End Sub
Here is a sample workbook with a better explanation of what I would like to achieve. Thanks for your code, it is almost what I need. I would like to code to actually do:
If Text "Paste" in one of the cell for Range from D1:AJ1
Copy and paste the corresponding column from Row 2 to 46
then delete Text "Paste" in cell D1
Thanks again for your help Alan!
 

Attachments

AlanSidman

Well-Known Member
Code:
Sub APM()
    Dim i As Long
    For i = 4 To 26
        If Cells(1, i) = "Paste" Then
            Cells(1, i).EntireColumn.Copy
            Cells(1, i).PasteSpecial xlPasteValues
            Cells(1, i).ClearContents
            Exit For
        End If
    Next i
End Sub
 
Top