How to Handle when Range to copy is blank?

[shellp]

Hello

I have written code that filters and copies a range correctly EXCEPT when there is no data to copy. I've tried if count > 0 then ....but it isn't working. Below is the code that works until there isn't data to meet the criterion.

Sub Chart_list()
Dim src as worksheet
Dim tgt as worksheet
Dim filterrange as range
Dim copyrange as range
Dim lastrow as long

Set src = Sheets("Chart_Listing")
Set tgt = Sheets("Chart_Export")

If tgt.range("B11:B11").value <> "" then
tgt.range("B11:H50").entirerow.delete
End If

src.autofiltermode=false
lastrow=src.range("A" & src.rows.count).end(xlup).row

set filterrange=src.range("A1:G" & lastrow)
set copyrange=src.range("A2:G" & lastrow)

filterrange.autofilter field:=1, Criteria1=tgt.range("C7:C7").value
filterrange.autofilter field:=6, Criteria1=tgt.range("M1:M1").value

copyrange.specialcells(xlcelltypevisible).copy tgt.range("B11:H100")

End Sub

P.S. why can I not paste into posts?

 

[Fluff]

Try

On Error resume next
copyrange.specialcells(xlcelltypevisible).copy tgt.range("B11:H100")
On Error goto 0

 

[shellp]

Ta da! Thank you so much!! Is there a way to get a message box to say "no charts found"?

 

[Fluff]

How about

   On Error GoTo nocharts
   CopyRange.SpecialCells(xlCellTypeVisible).Copy tgt.Range("B11:H100")
   On Error GoTo 0
   
   Exit Sub
nocharts:
   MsgBox "No charts found"
End Sub

 

[shellp]

Hi

Thank you so much!! Works great!

Shelley

 

[shellp]

Sorry, I lied. It seems that now when there are rows of data to copy, it is being copied multiple times i.e. row 1 to 5 then 1 to 5 and so on x 30. I didn't change anything other than what you advised. Thanks.

 

[Fluff]

Do you have any sheet or workbook event code?

 

[shellp]

Workbook code on "sheetactivate" so that the attention to a specific cell occurs when the user enters i.e. sh.range("B2").select

Worksheet code for worksheet_Calculate to hide/how an object based on what year is selected.

For the code above for the copy/paste it is a macro that is called via a command button. Thanks.

 

[shellp]

Seems the original code wasn't working correctly but not sure how to fix. The number of rows returns matches what is in the copyrange line i.e. B11:B100 or B11:B150 results in the copy rows being repeated until it meets row 100 or 150 or whatever. How can I change this? Thanks.

 

[Fluff]

Try

copyrange.SpecialCells(xlCellTypeVisible).Copy tgt.Range("B11")