Copy/Paste Values Based on Values in Column Header

[samuelm]

Hello,

I am looking to write a macro that is able to copy values in rows 3 to 10 if the word "copy" is in Row 1. For example, if Copy was in A1 it would copy A2:A10, but for all columns A through Z.

Something along the lines of:

If Range("A1").Value = "Copy" Then
    Range("A2:A7").Copy

Except that that preferably I could write the macro to copy all columns with "Copy" in Row 1 and not just in Column A.

Thanks!

 

[Joe4]

Welcome to the Board!

You seem to have some incomplete and conflicting information.

First, you mention copying from rows "3 to 10", though you then say "it would copy A2:A10" and your example shows "A2:A7". So not sure which rows you really want to copy.
You also don't mention where you want to copy them to.

However, maybe the following bit of information is all that you need and you can figure out the rest.
In order to loop through cells A1:Z1 and copy rows 2:10 from any column that has the word "Copy" in row 1, your could structure it like this:

Dim c as Long
For c = 1 to 26
    If Cells(1, c) = "Copy" Then
        Range(Cells(2, c), Cells(10, c)).Copy
        ...
    End If
Next c

 

[samuelm]

Sub Copy()
    Dim xRg As Range
    Dim xRgUni As Range
    Dim xFirstAddress As String
    Dim xStr As String
    On Error Resume Next
    xStr = "Name"
    Set xRg = Worksheets("Planning").Range("B293:BX293").Find(what:="Copy", LookIn:=xlValues, LookAt:=xlWhole)
    If Not xRg Is Nothing Then
        xFirstAddress = xRg.Address
        Do
            Set xRg = Worksheets("Planning").Range("B293:BX293").FindNext(xRg)
            If xRgUni Is Nothing Then
                Set xRgUni = xRg
            Else
                Set xRgUni = Application.Union(xRgUni, xRg)
            End If
        Loop While (Not xRg Is Nothing) And (xRg.Address <> xFirstAddress)
    End If
    xRgUni.Select
Selection.Offset(2, 0).Resize(Selection.Rows.Count + 8).Copy
End Sub

I was able to re-purpose this, and it is working well. Copies the correct cells. The problem I am now having is that it only works if the script is run on the sheet Planning. If I am on another sheet (I would like to be able run from a different one) then it tries to copy from random cells on the active sheet. Any ideas?

 

[Joe4]

If you want to run it from any sheet, you need to remove all references to specific sheets, i.e.

Worksheets("Planning")

If you leave off the sheet reference, it will default to the active sheet.

Also, be sure that you store the VBA code in a General VBA module, and NOT in one of the Sheet modules.

 

[Joe4]

One other thing.

I HIGHLY recommend you choose a different name for your procedure. You should NEVER used reserved words like "Copy" (reserved words are words that Excel/VBA already uses for their own functions, methods, properties, objects, etc) as the name of your procedures, functions, or variables. Doing so can cause errors and unexpected results.

To avoid this, I often add the prefix "My" to my procedure names, i.e. "MyCopy".