Based on the status, copy-paste entire row on another sheet

[VBABEGINER]

Dear Team,
I have one sheet where col H contains "open" or "close" status. What i'm trying in vba is wherever status is "open" in col H, all that records paste in "Sheet1".

Can anyone pls help on this vba code.. Thanks in advance..

 

[vcoolio]

Hello VBABeginer,

Try the following code:-

Sub Test()

Application.ScreenUpdating = False

With Sheet1.Range("H1", Sheet1.Range("H" & Sheet1.Rows.Count).End(xlUp))
        .AutoFilter 1, "Open"
        .Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
        .AutoFilter
End With

Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[VBABEGINER]

Hi, Thanks for immediate code assistance. But im trying to place name of the sheet instead of "Sheet1.Range",

for ex.- WBO is sheet name

With WBO.Range("H1", WBO.Range("H" & WBO.Rows.Count).End(xlUp))

and data starts from row 3 in WBO sheet

 

[vcoolio]

Hello VBABeginer,

Try it as follows:-

Sub Test()

         [COLOR=#ff0000]Dim sh As Worksheet: Set sh = Sheets("WBO")[/COLOR]
        
Application.ScreenUpdating = False

With [COLOR=#ff0000]sh[/COLOR].Range("H2", [COLOR=#ff0000]sh[/COLOR].Range("H" & [COLOR=#ff0000]sh[/COLOR].Rows.Count).End(xlUp))
        .AutoFilter 1, "Open"
        .Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
        .AutoFilter
End With

Application.ScreenUpdating = True

End Sub

I'm assuming that your headings are in row2 seeing that your data starts in row3.

It would still be wiser to use the sheet code rather than the sheet name.

What about the destination sheet (Sheet2)? Does it have a name also? If so, you can declare a variable for it and change Sheet2 in the code to suit. But make sure it is not the same variable as the source sheet(sh) : the parts in red font above.

I hope that this helps.

Cheerio,
vcoolio.

 

[VBABEGINER]

Hi vcoolio,
Thanks for your reply. It's really nice of you..

I'm getting an error on-

.Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)

Error - Object required

one more, what is this -- (3)(2)

 

[vcoolio]

Hello again,

I've just tested the code on a sample of what I assume your "WBO" sheet looks like and I am not receiving any errors.
Did you copy and paste the code into a module or did you type it in?

........or you are using a name for your destination sheet rather than the sheet code (Sheet2).
........or the sheet code is not Sheet2.
You'll need to clarify this.

one more, what is this -- (3)(2)

It is just an abbreviation.

It is the same as:

.Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count)[COLOR=#ff0000].End(xlUp).Offset(1)[/COLOR]

The (3) is the enumeration for the upward search from the bottom (xlUp) and the (2) is the enumeration for the offset of one row down from the previous row.

Cheerio,
vcoolio.

 

[VBABEGINER]

Did you copy and paste the code into a module = Yes. I used module.


----
Yes, it works perfectly. The sheet i wanted to paste on, its like name is Sheet2 but its index name as Sheet3.

Thanks... This is great help..

 

[vcoolio]

Hello VBABeginer,

Good to know its all working now.

You're welcome. I'm glad that I was able to help.

Cheerio,
vcoolio.