Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
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 June 28th, 2011, 02:09 PM
Registered User
Join Date: Jun 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Copy Column to another Worksheet

Hi everyone!
Any help would be MUCH appreciated!

I have to search a column for "BP1" and/or "BP2". Once I find it, I must copy that entire row ... then proceed to paste that row into a separate worksheet. I must do this for all instances of "BP1" and/or "BP2". Here is what I have so far... but it is not working..

Sub Copy_BP_Data()

x = 0

For Each cl In Sheets("Lookup").Range("F4:F500") 'look in column F
If cl.Value = "" Then
x = x + 1
End If

If cl.Value = "BP1" Then 'Search for "BP1"
d = 5
j = 3
ActiveCell.EntireRow.Copy Sheets("Bipole1").Rows(j) 'Paste into other ws
End If
j = j + 1
d = d + 1

If cl.Value = "BP2" Then
h = 5
i = 3
ActiveCell.EntireRow.Copy Sheets("Bipole2").Rows(i)
h = h + 1
i = i + 1

If cl.Value = "BP1/2" Then
k = 5
l = 3
ActiveCell.EntireRow.Copy Sheets("Bipole1").Rows(k)
ActiveCell.EntireRow.Copy Sheets("Bipole2").Rows(k)
End If
k = k + 1
l = l + 1

End If

Next cl \
End Sub
Old July 9th, 2011, 11:09 PM
Friend of Wrox
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts


Since you are basically searching for couple of words and copying the text, I would recommend you use the AutoFilter and copy the visible content to the other sheet. This will be pretty fast for your requirement

C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy worksheet to another workbook and rename it Probleminfinity Excel VBA 1 August 19th, 2007 11:38 PM
Data rewriting/copy between worksheet zone Excel VBA 0 August 9th, 2007 11:40 PM
Copy Worksheet into another workbook money Excel VBA 4 March 8th, 2007 05:17 AM
Copy a worksheet range to a new workbook cej2583 Excel VBA 2 March 14th, 2006 11:55 PM
Copy a Worksheet as HTML pakman Excel VBA 3 April 14th, 2005 11:53 AM

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