Trying to copy a range defined by an integer variable

[svw]

I'm fairly new to VBA, but feel I have learned quite a bit in a short amount of time. I normally can solve my problems using the macro recorder and searching online, but here I'm stumped.

I have two worksheets with data. I want to compare the two worksheets and pull matching data from both worksheets and compare on a third worksheet. I've used the application.match command in the past in other modules with this data and it has worked, however it is not working well with this set. I don't get any errors, it just only identifies and matches less than half of what it should. So, I've written a new procedure, but I keep getting the error "Application-defined or object-defined error"

To get around the application.match problem what I did was write the macro to copy the data from the worksheet with the static set of data, paste into the new worksheet, run a match function to locate the row of the same data in the other worksheet, and delete the rows where no match is found.

My next step is to use the row location created using the match function to identify the row of data from the other worksheet I would like to copy into the new (results) sheet. This is where the problem comes in.

I tried installing the VBCodeHTML add in, but I need administrator privlidges to paste into the addins folder, which I don't have. So here's some of my code:

Set OAReportData = Sheets("REPORTforOA").Range("A2:T101") 'data set is static
 
'copy the static data to the results worksheet
Sheets("OAPlanResults").Select
Range("A3").Select
OAReportData.Copy Destination:=Sheets("OAPlanResults").Range("A3")
 
'identify the location of the data to copy on the plan report worksheet
        Range("U3").Select
        ActiveCell.FormulaR1C1 = "=MATCH(OAPlanResults!RC[-12],OAPlanReport!C[-19],0)"
        Selection.AutoFill Destination:=Range("U3:U101")
 
 'delete rows where no match is found
     With Sheets("OAPlanResults")
        i2 = 3
        Set DiffCriteria = Sheets("OAPlanResults").Range("A3:U101")
 
        Dim c As Range
        Dim SrchRng
 
        Set SrchRng = ActiveSheet.Range("U3:U101")
        Do
            Set c = SrchRng.Find("#N/A", LookIn:=xlValues)
            If Not c Is Nothing Then c.EntireRow.Delete
        Loop While Not c Is Nothing
 
 
 
 
    End With
 
    'with data where a match is found copy the data based in the location specified in column U
    With Sheets("OAPlanResults")
 
        i2 = 3
 
        Dim rownum As Integer
        rownum = Sheets("OAPlanResults").Cells(i2, "U").Value
 
        Sheets("OAPlanResults").Select
        Range("W3").Select
 
        For i2 = 3 To 101 Step 1
 
'here is where the error occurs
 
        Sheets("OAPlanReport").Range(Range("A" & rownum), Range("Z" & rownum)).Select
        Selection.Copy
 
        Sheets("OAPlanResults").Range("W" & i2).Paste
 
        Next i2
 
        End With

Can you figure out what I"m doing wrong?

 

[svw]

I have figured out how to select the correct row, however now I am having a problem pasting the selected range. I get an error stating "Select Method of Range Class Failed" here is my new code:

With Sheets("OAPlanResults")
        
        i2 = 3
        
        Dim rownum As Integer
        Dim rownumrng As Range
        rownum = Sheets("OAPlanResults").Cells(i2, "U").Value
                
        Sheets("OAPlanResults").Select
        Range("W3").Select
        
        For i2 = 3 To 101 Step 1
        
        Sheets("OAPlanReport").Select
        Range("A1:Z1").Offset(rownum - 1, 0).Select
        Selection.Copy
        
'ERROR OCCURS HERE        
        Sheets("OAPlanResults").Cells(i2, "W").Select
        Selection.Paste
        
        Next i2
        
        End With

What gives?

 

[svw]

Changed the code up a bit. Simplified the paste syntax

       With Sheets("OAPlanResults")
 
          i2 = 3
 
        Dim rownum As Integer
        Dim lastrow As Integer
 
        lastrow = Sheets("OAPlanResults").Range("A" & Rows.Count).End(xlUp).Row
 
 
        Sheets("OAPlanResults").Select
        Range("W3").Select
 
        For i2 = 3 To lastrow Step 1
 
 
        rownum = Sheets("OAPlanResults").Cells(i2, "U").Value
        Sheets("OAPlanReport").Select
        Range("A1:Z1").Offset(rownum, 0).Select
        Selection.Copy Destination:=Sheets("OAPlanResults").Range("W" & i2)
 
 
        Next i2
 
        End With

It workes fine, with the exeption it is returning the next row, (i.e. row 31 instead of row 30).

 

[svw]

Got it. Thanks for all the help

    With Sheets("OAPlanResults")
        
          i2 = 3
        
        Dim rownum As Integer
        Dim lastrow As Integer
        
        lastrow = Sheets("OAPlanResults").Range("A" & Rows.Count).End(xlUp).Row
       
                
        Sheets("OAPlanResults").Select
        Range("W3").Select
        
        For i2 = 3 To lastrow Step 1
      
        
        rownum = Sheets("OAPlanResults").Cells(i2, "U").Value
        Sheets("OAPlanReport").Select
        Range("A1:Z1").Offset(rownum - 1, 0).Select
        Selection.Copy Destination:=Sheets("OAPlanResults").Range("W" & i2)
                   
        
        Next i2
        
        End With