Newbie needs help

LWellman · February 9th, 2011, 06:44 PM

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.
Example:
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!

LWellman · February 10th, 2011, 11:32 AM

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?

Code:

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
        Do

            ' 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

Thanks!!
Leta

mtranchi · February 17th, 2011, 01:53 PM

you use the resize method of the range object.

Code:

'this would refer to the current cell and 7 cells to the left:
rgMyRange.Resize(1, 8)

hope that helps :-)