Frustrated

[cooliebush]

I am new to VBA. Know some but not much.
What i would like to know is the following:

i need to look for an unknown value in a row(columnA), compare it to the row below in the same column. If these cells are the same to copy all data from column C to L in same row to another workbook. i can do it with one row. The problem comes about when there is more than one row in column A that has the same value and i need to copy the data in column C to L that has the same value in column A to another workbook. i also need to loop so that it checks all rows. There are a variable number of rows each time and the values in column A are never the same each time.
I have most of my code, all i need is some code to compare the rows beneath each other then select multiple rows where values in colmun A are the same and for the macro to loop through all cells.

Hope it is not much to ask

 

[AgentSmith]

I'm not entirely sure what this "unknown value" is that you mention, but here's some ideas.

1) Use AutoFilter. Filter column A for the value you want then copy the required range manually.

2) Do what you've asked for! :wink: ...

Public Sub CopyMacro(ByVal pRequiredValue As Double)
    Dim lngInputRow As Long, lngOutputRow As Long
    Dim wbkCopyFrom As Workbook, wbkPasteTo As Workbook
    
    Set wbkCopyFrom = ThisWorkbook
    Set wbkPasteTo = Workbooks.Add(xlWBATWorksheet)
    
    'Select the required sheet
    Sheets("Sheet1").Select
    
    wbkCopyFrom.Activate
    lngOutputRow = 1
    For lngInputRow = 2 To ActiveSheet.UsedRange.Rows.Count
        With ActiveSheet.Cells(lngInputRow, 1)
            If .Value = pRequiredValue Then
                
                'Copy the contents on the same row in columns C to L
                .Range("C1:L1").Select
                Selection.Copy
                
                'Switch to the destination workbook and move down a row
                wbkPasteTo.Activate
                lngOutputRow = lngOutputRow + 1
                Cells(lngOutputRow, 1).Select
                
                'Paste it
                ActiveSheet.Paste
                Application.CutCopyMode = False
                
                'Return to the source workbook
                wbkCopyFrom.Activate
            End If
        End With
    Next
    
    'Tidy up
    Set wbkCopyFrom = Nothing
    Set wbkPasteTo = Nothing
End Sub

You'll probably need to change the row number that the loop starts at. Currently the first cell it looks at is A2 (row 2, column 1). I've also assumed the "unknown value" is a number (I used Double type), but you can change it as required.

Also, it's currently outputting all the results to a new spreadsheet. If you have a target spreadsheet in mind then you'll need to change wbkPasteTo accordingly, as well as the starting value of lngOutputRow.

Lastly, if your columns C to L contain formulae, bear in mind that the references might be lost when you copy them across. You might want to "PasteSpecial xlValues" instead.

Hope this is on the right track.

 

[cooliebush]

thanks

will try it and see if it works

thanks for the speedy reply