CODEWITHSUNDEEP
vbaexcel
ggmkp
ggmkp

Reputation: 725

Find, Copy & Paste Values under Multiple Columns

Below code is for copying values under "Apple" Column in sheet1 to "AppleNew" Column in sheet2. (Thanks to Tim)

But If I have multiple columns (Orange, Banana etc) is there way to write more simpler code that sort of go through the loop instead of having to copy and paste code for the each columns?

Dim rng as range, rngCopy as range, rng2 as range

set rng = Sheet1.Rows(3).Find(What:="Apple", LookIn:=xlValues, LookAt:=xlWhole)

if not rng is nothing then

    set rngCopy = Sheet1.range(rng.offset(1,0), _
                               Sheet1.cells(rows.count,rng.column).end(xlUp))

    set rng2 = Sheet2.Rows(1).Find(What:="AppleNew", LookIn:=xlValues, _
                                   LookAt:=xlWhole)

    if not rng2 is nothing then rngCopy.copy rng2.offset(1,0)

end if

Upvotes: 0

Views: 420

Answers (2)

Tim Williams
Tim Williams

Reputation: 166306

sub Tester()

    DoColumnCopy "Apple", "AppleNew"
    DoColumnCopy "Apple2", "Orange"

end sub

sub Tester2()
   dim i, arrFrom, arrTo

   arrFrom = Array("Apple","Apple2") 'source cols
   arrTo=Array("AppleNew","Orange")  'destination cols

   for i=lbound(arrFrom) to ubound(arrFrom)
       DoColumnCopy Cstr(arrFrom(i)), Cstr(arrTo(i)) 'EDIT: pass as strings
   next i
end sub




Sub DoColumnCopy(FromColName as string, ToColName as string)

    Dim rng as range, rngCopy as range, rng2 as range

    set rng = Sheet1.Rows(3).Find(What:=FromColName , LookIn:=xlValues, _
                                  LookAt:=xlWhole)

    if not rng is nothing then

        set rngCopy = Sheet1.range(rng.offset(1,0), _
                        Sheet1.cells(rows.count,rng.column).end(xlUp))

        set rng2 = Sheet2.Rows(1).Find(What:=ToColName , LookIn:=xlValues, _
                                   LookAt:=xlWhole)

        if not rng2 is nothing then rngCopy.copy rng2.offset(1,0)

    end if

end sub

Upvotes: 1

tigeravatar
tigeravatar

Reputation: 26640

Dim varColName As Variant

For Each varColName In Array("Orange", "Banana", "Pear")

    'Your code goes here
    'In your code, replace "Apple" with varColName
    'In your code, replace "AppleNew" with varColName & "New"

Next varColName

Upvotes: 1

Related Questions