Copy a Column, only if contains certain value

Microsoft Excel 2003 (full product)
July 13, 2010 at 07:43:00
Specs: Windows XP
Right now i have a macro that detects a value "GRM" in a worksheet and selects it, now.. i am looking to get the code to detect "GRM" and then copy the whole column's contents and paste it to another worksheet. Im new to this and would love the answer for this

See More: Copy a Column, only if contains certain value

Report •


#1
July 13, 2010 at 08:32:20
Hi,

Without knowing how your code is finding the cell and what the variable it is set to, I can't be specific, so here is a fairly generic 'find' for your text, followed by Copying the whole column to another worksheet in the same workbook:

Sub ColCopy()
Dim rngFind As Range

With Worksheets("Sheet1").UsedRange
Set rngFind = .Find("GRM", _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=True)
End With
rngFind.EntireColumn.Copy _
                Destination:=Worksheets("Sheet2").Range("A1")
End Sub

Note that in VBA it is rarely necessary to 'Select' a cell or object before acting on it.

The Copy function above is an 'all-in-one' copy and paste. The "_" character is a line continuation character, so that the line of code can be wrapped onto two lines.

When copying a column, or row, the destination must be a single cell in row 1 or column 1.
If you had wanted this column copied to column B on worksheet "Sheet2" you would have used B1 instead of A1.

There is code that you can use to find the empty column after the last used column, so that successive copies go to sequential columns.

Sub ColCopy()
Dim rngFind As Range
Dim rngDest As Range

With Worksheets("Sheet1").UsedRange
Set rngFind = .Find("GRM", _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=True)
End With
'find empty column on sheet2 based on row 1 always containing data.
Set rngDest = Worksheets("Sheet2") _
            .Cells(1, Application.Columns.Count) _
            .End(xlToLeft).Offset(0, 1)
'copy to empty column
rngFind.EntireColumn.Copy Destination:=rngDest
End Sub

Regards
PS You shouldn't post your e-mail address in a public forum


Report •

#2
July 13, 2010 at 08:41:25
this is great! thanks a lot for your help and thanks for the e-mail advice!

Report •

#3
July 13, 2010 at 09:11:40
Hi,

You're welcome.

Regards

Humar


Report •
Related Solutions


Ask Question