Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 5th, 2011, 11:49 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default VBA Find and copy

HI,

I was wondering if anyone knew whether it is possible to build a macro which will loop through a list on sheet1, then for each cell in that list perform a find on the range a:a in sheet2. For each each row it finds in sheet2, copy the row and paste it in a new line in sheet3.

Currently i have the following however its not performing the copy and paste section:

Code:
Dim BacSht1 As Worksheet
Dim BacSht2 As Worksheet
Dim BacSht3 As Worksheet
Dim Broker As String
Dim Cell As Range
Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddr As String

Set Bac = Workbooks.Open("O:\Emma's Handover\Remittance Advice\bacscomms.csv")

Bac.Sheets.Add.Name = "Sheet1"
Bac.Sheets.Add.Name = "Sheet2"

Set BacSht1 = Bac.Sheets("bacscomms")
Set BacSht2 = Bac.Sheets("Sheet1")
Set BacSht3 = Bac.Sheets("Sheet2")

For Each Cell In BacSht2.Range("A:A")

With BacSht1.Range("A:A")
    Set LastCell = .Cells(.Cells.Count)
End With
Set FoundCell = BacSht1.Range("A:A").Find(what:="a", after:=LastCell)

If Not FoundCell Is Nothing Then
    FirstAddr = FoundCell.Address
End If
Do Until FoundCell Is Nothing
    FoundCell.Copy
    BacSht3.Select
    Range("B1").PasteSpecial
    Set FoundCell = BacSht1.Range("A:A").FindNext(after:=FoundCell)
    If FoundCell.Address = FirstAddr Then
        Exit Do
    End If
    
Loop

Next Cell
Has anyone got any ideas?

Thank you!

Jeskit





Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Copy Help ks1102 Excel VBA 5 February 10th, 2009 11:08 PM
Excel VBA copy & paste wensmail Excel VBA 1 September 17th, 2007 11:13 PM
copy one record to another table using VBA kitaeshi Access VBA 1 June 11th, 2007 06:50 AM
Excel VBA Problems with copy and paste bripers Excel VBA 6 September 1st, 2006 05:14 AM
Copy data from webpage to excel using vba kg8299 Excel VBA 0 April 5th, 2006 03:51 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.