next empty row

[Ossian13]

Hello everyone,

can anyone tell me the following:
I have a sheet with a header, and my macro is copying rows to the respective sheet but it keeps pasting over my header as i caould not find a solution to move to the next available empty row.

I have tried this but it has no effect

nextrow = Sheets("OnlyV").Range("A" & Rows.Count).End(xlUp).Row + 1

THank you,
Ossian

 

[Joe4]

That code will find the the next available row in column A, but the question then, is how exactly are you trying to use this calculated value in your pasting code?
You need to post more of your code so we can see how you are trying to use it.

 

[Ossian13]

Range("A1").ActivateActiveCell.EntireRow.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "OnlyV"
ActiveSheet.Paste
Cells.EntireColumn.AutoFit


Application.Worksheets("Sheet1").Activate


    Cells.Find(What:="Michael", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Select
    ActiveCell.EntireRow.Copy
    Application.Worksheets("OnlyV").Activate
   nextrow = Sheets("OnlyV").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
   ActiveSheet.Paste

Here it is. I want every row which contains the name Michael to be copied to the sheet "OnlyV", and everytime on the next row not overwrite over the 1st row.
Hope this helps.
Thanks,
Ossian.

 

[Joe4]

Just as I thought. You are calculating the value of the nextrow, but then not doing anything with it!
Storing the value in a variable does not move or select anything.

Try changing this line:

nextrow = Sheets("OnlyV").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

to

Sheets("OnlyV").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select

which actually select the cell that you want to paste to.

If you had wanted to use the variable, you could do:

nextrow = Sheets("OnlyV").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Sheets("OnlyV").Range("A" & nextRow).Activate

but that is an unnecessary extra step.

 

[Joe4]

You could actually simplify the whole code to this:

    Range("A1").EntireRow.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "OnlyV"
    ActiveSheet.Paste
    Cells.EntireColumn.AutoFit

    Worksheets("Sheet1").Activate
    Cells.Find(What:="Michael", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.EntireRow.Copy Sheets("OnlyV").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

 

[Ossian13]

I thank you very much for your kind effort! It works! Thumbs up!

 

[Joe4]

You are welcome!

I hope my explanation of why it didn't work initially makes sense.

 

[Ossian13]

Yes, you made it very clearly and it is indeed making sense.

 

[Ossian13]

I would like to ask one more thing, perhaps you @Joe4 can help me with it.
How do I insert a range of columns where the find function to look only? For example i want the macro to search for the name Michael starting from column AO to column AT and not all over the entire sheet.
Thanks!

 

[Joe4]

Just change the range reference in your Find function:

[COLOR=#ff0000]Cells[/COLOR].Find(What:="Michael", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

to

[COLOR=#FF0000]Range("AO:AT")[/COLOR].Find(What:="Michael", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate