2 sheets with different finalrow variables

[ShaunD30]

Ok...Trying to get a "do until" loop.

I have

Finalrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Finalrow2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

I need my "Do until" loop to run until the activecell in sheet1 =finalrow

I tried "Do until activecell.row=Finalrow", however that didn't work because it ended my macro too soon.

I tried "Do until sheets("Sheet1").Activecell.row=Finalrow
But it won't accept that.

any idea on how I need to word this so it works?

Shaun

 

[ShaunD30]

additional info........

The code I am working on takes a value in say.."G2" on sheet 1..
And searches for all records with that value in sheet2, and pastes those records into sheet 3
Once the code has reached finalrow2 in sheet 2, it is supposed to go back to sheet 1 and offset 1 and continue searching for the next records.

I don't want this loop to end until sheet1 has reached finalrow.

 

[VoG]

What you need to do fundamentally is to not select/activate cells in your loop. You can loop without selecting like this:

For i = 1 To LastRow
    If Range("A" & i).Value = 1 Then 'do something
Next i

 

[ShaunD30]

Yeah I got to thinkin about that after I posted..and I came up with

Sub GetData()
Sheets("Sheet1").Select
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row
Finalrow2 = Sheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row

Range("B2").Select
Curzip = ActiveCell.Value
Do Until ActiveCell.Row = Finalrow
For I = 2 To Finalrow2
Sheets("Sheet4").Select
If Cells(I, 7).Value = Curzip Then
    Cells(I, 2).Copy
    Sheets("Sheet3").Select
    Range("A1").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
        ActiveSheet.Paste
        Else
    End If
    Next I
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Select
Curzip = ActiveCell.Value
Loop
End Sub

I think it works..testing now

 

[VoG]

Does this work?

Sub GetData()
Dim Finalrow As Long, Finalrow2 As Long, i As Long, j As Long
Dim Curzip As Variant
With Sheets("Sheet1")
    Finalrow = .Cells(Rows.Count, 1).End(xlUp).Row
    Finalrow2 = Sheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row
    For j = 2 To Finalrow
        Curzip = .Range("B" & i).Value
        For i = 2 To Finalrow2
            If Sheets("Sheet4").Cells(i, 7).Value = Curzip Then
                Sheets("Sheet4").Cells(i, 2).Copy Destination:=Sheets("Sheet3").Range("A1").End(xlDown).Offset(1)
            End If
        Next i
    Next j
End With
End Sub

 

[ShaunD30]

I get a

"Run-time error '1004':

Application-defined or object-defined error"

And it highlights

Curzip = .Range("B" & i).Value

Any ideas? I get your solution..just not sure why it's giving an error here.

Shaun

 

[ShaunD30]

I fixed..should be "Curzip=.range("b" & j).Value

Thanks VoG! I didn't know you could do multiple for/next like that.

Appreciate your help as always

Shaun

 

[ShaunD30]

Am getting the same error message for this-

Sheets("Sheet4").Cells(i, 2).Copy Destination:=Sheets("Sheet3").Range("A1").End(xlDown).Offset(1)

 

[VoG]

This should work

Sub GetData()
Dim Finalrow As Long, Finalrow2 As Long, i As Long, j As Long
Dim Curzip As Variant
With Sheets("Sheet1")
    Finalrow = .Cells(Rows.Count, 1).End(xlUp).Row
    Finalrow2 = Sheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row
    For j = 2 To Finalrow
        Curzip = .Range("B" & j).Value
        For i = 2 To Finalrow2
            If Sheets("Sheet4").Cells(i, 7).Value = Curzip Then
                Sheets("Sheet4").Cells(i, 2).Copy Destination:=Sheets("Sheet3").Range("A1").End(xlDown).Offset(1)
            End If
        Next i
    Next j
End With
End Sub

 

[ShaunD30]

Still getting same error with the destination= line.

am running 2003 if that helps.

Thanks

Shaun