Copies range from the wrong sheet

[Sad Bills Fan]

Hello, first post. I'm trying to search a set of data on a sheet called Sold Data and match it to a variable from a cell in a sheet called priceComps. I've defined the sheets as ws and reportSheet resepctively. If there's a match i want to copy the entire line to the priceComps sheet. My problem is no matter what i do, it tries to copy from the priceComps sheet instead of the Sold Data sheet. I've used multiple ws.select and ws.activate commands but it still goes back to the wrong sheet.

Private Sub cmdSearch_Click()

Dim wb As Workbook
Dim ws As Worksheet 'where the data is copied from
Dim reportSheet As Worksheet 'where the data is pasted to
Dim bedrooms As String
Dim bathrooms As String
Dim zip As String
Dim test As String

Dim finalRow As Integer 'the last row of the dataset (if more than 30,000 needs to be type long instead of integer)
Dim i As Integer 'row counter

Set wb = ThisWorkbook
Set ws = Worksheets("Sold Data")
Set reportSheet = Worksheets("priceComps")

'clear old data from report sheet
reportSheet.Range("A12:AA5000").ClearContents 'adjust if it passes 5,000 lines

bedrooms = reportSheet.Cells(2, 3).Value
bathrooms = reportSheet.Cells(4, 3).Value
zip = reportSheet.Cells(8, 3).Value

'go to datasheet and start searching and copying

finalRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To finalRow
test = ws.Cells(i, 9).Value
    If bedrooms = test Then
        ws.Select
        Range(Cells(i, 1), Cells(i, 27)).Copy
        ws.Select
        'reportSheet.Select
        Range("A5000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
        ws.Select
    End If
Next i

reportSheet.Select

Range("B2").Select

MsgBox ("Search Complete")
        

End Sub

 

[HighAndWilder]

Hello, first post. I'm trying to search a set of data on a sheet called Sold Data and match it to a variable from a cell in a sheet called priceComps. I've defined the sheets as ws and reportSheet resepctively. If there's a match i want to copy the entire line to the priceComps sheet. My problem is no matter what i do, it tries to copy from the priceComps sheet instead of the Sold Data sheet. I've used multiple ws.select and ws.activate commands but it still goes back to the wrong sheet.

Private Sub cmdSearch_Click()

Dim wb As Workbook
Dim ws As Worksheet 'where the data is copied from
Dim reportSheet As Worksheet 'where the data is pasted to
Dim bedrooms As String
Dim bathrooms As String
Dim zip As String
Dim test As String

Dim finalRow As Integer 'the last row of the dataset (if more than 30,000 needs to be type long instead of integer)
Dim i As Integer 'row counter

Set wb = ThisWorkbook
Set ws = Worksheets("Sold Data")
Set reportSheet = Worksheets("priceComps")

'clear old data from report sheet
reportSheet.Range("A12:AA5000").ClearContents 'adjust if it passes 5,000 lines

bedrooms = reportSheet.Cells(2, 3).Value
bathrooms = reportSheet.Cells(4, 3).Value
zip = reportSheet.Cells(8, 3).Value

'go to datasheet and start searching and copying

finalRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To finalRow
test = ws.Cells(i, 9).Value
    If bedrooms = test Then
        ws.Select
        Range(Cells(i, 1), Cells(i, 27)).Copy
        ws.Select
        'reportSheet.Select
        Range("A5000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
        ws.Select
    End If
Next i

reportSheet.Select

Range("B2").Select

MsgBox ("Search Complete")
       

End Sub

Unless you specify on which sheet the range is it will always use the active sheet.

You are copying and pasting from / to the same sheet

You don't need to select the range or even the worksheet to copy from and paste to if you specify the worksheet in the copy line.

e.g. Worksheets("Sheet1").Range("A1:D4").Copy Destination:=Worksheets("Sheet2").Range("E5:H8")

 

[Sad Bills Fan]

Thank you. That set me on the right path. It worked if the cells are identified but when i tried to make it dynamic i had to switch to a with statement.

If bedrooms = dataBdrms Then
    
        With ws
            .Range(.Cells(i, 1), .Cells(i, 27)).Copy
        End With
        
        With ws2
            .Paste Destination:=.Range("A5000").End(xlUp).Offset(1, 0)
        End With
    
    
        'Worksheets("Sold Data").Range("A4:AA4").Copy Destination:=Worksheets("priceComps").Range("A12:AA12")
        'Worksheets("Sold Data").Range(Cells(i, 1), Cells(i, 27)).Copy Destination:=Worksheets("priceComps").Range("A12:AA12")
    End If

 

[kevin9999]

Not exactly. The following one line should work:

ws.Range(ws.Cells(i, 1), ws.Cells(i, 27)).Copy ws2.Cells(Rows.Count, "A").End(xlUp).Offset(1)

Anyhow, I was curious - you set a couple of variables in your original post (bathrooms, zip) but then you don't seem to use them anywhere? Also, there's better ways of copying than doing it one row at a time. It looks like you want to copy any row from the Sold Data sheet where column I in the Sold Data equals the 'bedroom' value you get from the priceCompos sheet cell C2 and paste to the priceComps sheet - next available line? Is that right?

 

[Sad Bills Fan]

Not exactly. The following one line should work:

ws.Range(ws.Cells(i, 1), ws.Cells(i, 27)).Copy ws2.Cells(Rows.Count, "A").End(xlUp).Offset(1)

Anyhow, I was curious - you set a couple of variables in your original post (bathrooms, zip) but then you don't seem to use them anywhere? Also, there's better ways of copying than doing it one row at a time. It looks like you want to copy any row from the Sold Data sheet where column I in the Sold Data equals the 'bedroom' value you get from the priceCompos sheet cell C2 and paste to the priceComps sheet - next available line? Is that right?

Yes that's right. In the end I want to download sales history for a given area, then be able to pull comps based on bedrooms, bathrooms, and zip code. I was starting with just bedrooms to keep it simple. I couldn't get it to work with one line, but the with loop did work. What is the better method you recommend? I was wondering if I should i do a filter then copy everything instead?

 

[kevin9999]

I think it would be more efficient to copy the data en masse rather than one line at a time. Could you share both the Sold Data and priceComps sheets using the XL2BB add in, or alternatively share your file via Google Drive, Dropbox or similar file sharing platform? You can easily disguise any sensitive data, it's really just the structure that's needed.