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 February 9th, 2011, 06:44 PM
Registered User
Join Date: Feb 2011
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Default Newbie needs help

Hi all,
I'm a newbie at VBA. I have a workbook that has multiple sheets. The first sheet is the page where the user enters their data. I want to copy certain lines of that data (if UOM=DY) to a different sheet. I have named the range of the area where the user will enter the data.
I bought a couple of Excel VBA books, but I haven't been able to figure out how to do this.
DATA worksheet has 200 lines of data, 13 of which have a product with the UOM=DY. I want to copy ONLY those lines to SPECIALS worksheet. The named range is USERDATA.
Can anyone help me with this?
Thanks in advance!
Old February 10th, 2011, 11:32 AM
Registered User
Join Date: Feb 2011
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Default Update to the above

Hi All,
I found something in the archives that helped. Below is my coding. It works (more or less) the way I want it to, but now I want to JUST copy 8 columns of the row, not the entire row. How do I need to modify my code to reflect that?

Sub RentalsFinder()

Dim wksSearch As Worksheet
Dim wksOut As Worksheet
Dim intOutRow As Integer

Dim rngFound As Range
Dim strWhat As String
Dim strFirstAddress As String

    ' Turn off screen updating to increase performance
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Set wksSearch = ThisWorkbook.Sheets("Data for VA")
    Set wksOut = ThisWorkbook.Sheets("ValueAdded")
    intOutRow = 9

    ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ' Find the word DY
    ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    strWhat = "DY"

    Set rngFound = wksSearch.Columns(3).Find(strWhat)

    ' If we've found something then proceed
    If Not rngFound Is Nothing Then

        ' Record the first found address
        strFirstAddress = rngFound.Address

        ' Loop until we find no more or get back to the first found instance

            ' Copy across the values of the row
            wksOut.Cells(intOutRow, 1).EntireRow.Value = rngFound.EntireRow.Value

            ' Increment the output row counter
            intOutRow = intOutRow + 1

            ' Find the next instance
            Set rngFound = wksSearch.Columns(6).FindNext(rngFound)

        Loop Until rngFound Is Nothing Or rngFound.Address = strFirstAddress

    End If

    'Turn screen updating back on
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub
Old February 17th, 2011, 01:53 PM
Friend of Wrox
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default offset

you use the resize method of the range object.
'this would refer to the current cell and 7 cells to the left:
rgMyRange.Resize(1, 8)
hope that helps :-)
The Following User Says Thank You to mtranchi For This Useful Post:
LWellman (February 21st, 2011)

Similar Threads
Thread Thread Starter Forum Replies Last Post
Please help the newbie!! shelly.aix Visual Basic 2008 Essentials 2 December 16th, 2008 07:35 AM
C# newbie jazzcatone C# 1 November 29th, 2005 06:03 PM
Newbie please help indyanguy XSLT 1 September 2nd, 2005 09:18 AM
Newbie help please khalsted Visual C++ 1 June 24th, 2005 06:03 PM
newbie needs help, please godsallaroundus VB.NET 1 October 28th, 2004 11:08 PM

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