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 October 12th, 2007, 08:07 AM
Registered User
Join Date: Oct 2007
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default copying input data to another worksheet with code

Is it possible to have two worksheets in a workbook(input and output) to where if the code scans through all rows in column one, and if it recognizes a semicolon--copy the whole row to the second(output) worksheet?

Old October 12th, 2007, 09:18 AM
Friend of Wrox
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts

Here is an example

Sub Copy_Based_On_SemiColon()

    Dim oWB1 As Workbook
    Dim oWB2 As Workbook

    Dim bRet As Boolean

    Set oWB1 = ActiveWorkbook
    Set oWB2 = Workbooks.Add

    Dim i1 As Long
    Dim i2 As Long

    Dim arSemiColonAdd() As String
    Dim iRow

    bRet = FindAll(";", oWB1.Sheets(1), "A:A", arSemiColonAdd)

    i2 = 1
    If bRet = True Then

        For i1 = 1 To UBound(arSemiColonAdd)
            i2 = i2 + 1
            iRow = Right(arSemiColonAdd(i1), Len(arSemiColonAdd(i1)) - InStrRev(arSemiColonAdd(i1), "$"))
            oWB1.Sheets(1).Rows(iRow).EntireRow.Copy Destination:=oWB2.Sheets(1).Range("A" & i2)
        Next i1

    End If

End Sub

You need to tweak the oWB1 and oWB2 to suit your requirments

also copy the function

http://vbadud.blogspot.com/2007/10/e...ll-method.html to your code



VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Old November 2nd, 2007, 12:46 PM
Registered User
Join Date: Oct 2007
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts

hey buddy...this code worked great...but i have a few more questions about it and maybe more code being integrated into it.

My project is to make this machine work on this code and my boss is asking me to work in steps, since both him and I are new to VBA.

YOu have helped me with two pieces of code:

above post ^

http://p2p.wrox.com/topic.asp?TOPIC_ID=65807 (the first piece of code you helped me with)

I need to know (referring back up the original question)If you could help me with some code that scans through column 1 again, but this time, looks for the column 1(and only column 1) to be equal to "25", and if does not then again add said line(s) to the output workbook like in earlier code in this thread. (see original question in current thread)

any questions, we can talk over email if you'd prefer....

again, sorry for all the questions, but you are a great help with this stuff!!
Thanks in advance

btw....all this stuff is going to be one big piece of code, but i can place the code where it needs to be later on. These code pieces are for a circuit board pick and place machine.....we need to get the machine to intake this information and be able to comprehend it and pick parts and pieces and place them on the board.

thats what this is all about! if that helps at all! haha
Old November 2nd, 2007, 01:45 PM
Registered User
Join Date: Oct 2007
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts

also Shasur, in your "FindAll" sub routine, you wrote:

bRet = FindAll(";", oWB1.Sheets(1), "A:A", arSemiColonAdd)

Is that referring to Column 1 Row 1 or could you inform me on that really quick?

i have seen it written a couple of ways, and want to make sure i follow your code correctly.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying DOCTYPE in input to the output xml mrame XSLT 3 July 31st, 2008 04:26 PM
Copying code from one sheet to another jdd0x01 Excel VBA 0 May 28th, 2007 07:11 PM
Copying Rows to New worksheet eusanpe Excel VBA 7 December 21st, 2006 10:23 AM
searching match data in the worksheet ct Excel VBA 0 April 15th, 2006 12:00 AM
Does we can copying MSIL code to other platform? fuehrer General .NET 1 June 13th, 2005 12:07 PM

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