copying from one sheet to another, but skipping "0"

[cying]

Hi. I'm new to this forum AND macros in general (other than a c++ class in high school many years ago), so please bear with me. I have a feeling this is very simple, but massive amounts of googling has not helped me.

I have a worksheet with values in column A (names of cities) and numbers in column B (number or referrals branches in said cities we have received). Now many of these branches have not given any referrals yet, so their B column is 0.

I'm trying to write a macro to copy all the non-0 referrals from one sheet to another.

So basically:

Branch	Referrals
Atlanta Dunwoody	0
Austin (AUS)	1
AUSTIN DOWNTOWN	0
BELLEVUE (BEL)	1
Berkeley	0

<tbody>
</tbody>

Needs to be copied into a new sheet as:

Branch	Referrals
Austin (AUS)	1
BELLEVUE (BEL)	1

<tbody>
</tbody>

Thanks in advance!

Also, I currently have the 0's in column B hidden (format cells 0;-0;;@). And column B integers are from a countif from yet another sheet. I don't know if that would affect the macro or not.

EDIT:

I don't think this is possible as a simple IF function in the formula of the new sheet, but if it is please let me know too.

 

[Michael M]

Hi and welcome to the Forum
Have you considered simply using an Autofilter.
Data>>Autofilter>>filter for 1 in Column "B"
Then just copy the visible rows across to the other sheet

 

[cying]

Hi and welcome to the Forum
Have you considered simply using an Autofilter.
Data>>Autofilter>>filter for 1 in Column "B"
Then just copy the visible rows across to the other sheet

Hi thanks for the welcome. It's not all 1 or 2. The numbers go up to 14 and are ever increasing. I guess that wasn't the best example.

I also need it to be dynamic, since I would need the branch name to appear in the new sheet in the correct alphabetical order if a zero turns into a one or a two.

 

[Michael M]

Okay, so change the Filter to ANY number greater than 0.
AND
If the number changes from 0 to any other number OR you add a new row, simply rerun the filter.

 

[cying]

Okay, so change the Filter to ANY number greater than 0.
AND
If the number changes from 0 to any other number OR you add a new row, simply rerun the filter.

ok i guess that works. i felt like a macro would be better for future-proofing for when i hand this spreadsheet off to someone else to maintain.

thanks!

 

[Michael M]

Sorry, I wasn't trying to fob you off...I wanted to give you the easiest option !!
TRry this if you need to use a macro....it copies from sheet 1 to Sheet 2

Sub MYKL1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
For r = 2 To lr
    If Range("B" & r).Value >= 1 Then
        Rows(r).Copy Destination:=Sheets("Sheet2").Rows(lr2)
        lr2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
    End If
Next r
End Sub

 

[cying]

Sorry, I wasn't trying to fob you off...I wanted to give you the easiest option !!
TRry this if you need to use a macro....it copies from sheet 1 to Sheet 2

Sub MYKL1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
For r = 2 To lr
    If Range("B" & r).Value >= 1 Then
        Rows(r).Copy Destination:=Sheets("Sheet2").Rows(lr2)
        lr2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
    End If
Next r
End Sub

Wow this is almost exactly what I was looking for! Thanks so much. I wasn't trying to downplay your earlier efforts either. I was just worried a filter would cause problems for whoever is next down the line for this data set in the future.

One last question though. Could you tell me the changes I'd need to do to copy only columns A and B to Sheet2 versus the entire row?

If it's a huge hassle it's fine. This is just nitpicking for worksheet cleanliness.

Thanks again for all your help!

 

[Michael M]

Ok, there you go, just copies cols "A" & "B"

Sub MYKL1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
For r = 2 To lr
    If Range("B" & r).Value >= 1 Then
        Range("A" & r & ":B" & r).Copy Destination:=Sheets("Sheet2").Range("A" & lr2)
        lr2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
    End If
Next r
End Sub

 

[cying]

I was trying to change it myself, but there's no way I could've gotten Range("A" & r & ":B" & r).

Thanks so much for everything!