Microsoft Excel - Using VBA is there any way to see if a certain spreadsheet is filtered before I paste info

I have a master sheet that I put certain information. Having written a macro to copy this info to another sheet then insert a new row & paste the information. Everything works fine unless the recipient sheet has been left filtered.

If receipient sheet is filtered the the new row is created but pasted info is dropped into wrong place over-writing existing information.

If i write to just unfilter the recipient sheet and it is not filtered then I get error.

Is there a check of some sort i.e. if filtered = true then unfilter else "carry on with original macro"??

Hi Graham,

Try this

if Activesheet.Filtermode = false then
  'Do stuff
Else
  'Do other stuff
end if

Cheers

Pete

Hi Pete,
Sheet appeared to work at first if filtered then failed when it wasn't on next try.
I have played with several ways around but keeps failing, now I don't get my data in at all.
Any more help would be very much appreciated.

Using this at mo before pasting data:-

' Update_Jobstatus Macro
'
    Application.ScreenUpdating = False

    Sheets("Job Status").Select
' Add extra line into account status
' Unfilter sheet if required
If ActiveSheet.FilterMode = False Then

    Range("A4").Select
Else
'Do other stuff
    Range("Table7[[#Headers],[Init''s]]").Select
    ActiveSheet.ShowAllData
    Range("A4").Select
End If
    Selection.Activate
    ActiveSheet.ListObjects("Table7").ListRows.Add AlwaysInsert:=True
    Dim lngNextRow As Long, rngNextRow As Range
lngNextRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    Selection.Activate
' Copy New Job No from Quotes and paste to Job Status *******   etc etc

Hi Graham,

Do you know on what line its failing?

You could try adding the "on error resume next" command at the top of the sub.

This will bypass all errors and plough on through the code.

I don't normally recommend this as  I like to put all errors through an error handler first. You should always try to identify any errors and trap them incase the rest of you code wrecks something. But in this case, from what I can see in the sample, you only seem to be selecting cells.

Pete

I do not get a phisycal error message it is just that data pastes itself over existing rows.

I have changed it the filtering around a bit to this and the info does get posted from quotes to job status but the sheet does NOT unfilter??

' Update_Jobstatus Macro
'
    Application.ScreenUpdating = False

    Sheets("Job Status").Select
' Add extra line into account status
' Unfilter sheet if required
If ActiveSheet.FilterMode = True Then
'
    Range("A4").Select
    Range("Table7[[#Headers],[Init''s]]").Select
    ActiveSheet.ShowAllData
    Range("A4").Select
Else
'Do other stuff
    Range("A4").Select
End If
    Range("A4").Select
    Selection.Activate
    ActiveSheet.ListObjects("Table7").ListRows.Add AlwaysInsert:=True
    Dim lngNextRow As Long, rngNextRow As Range
lngNextRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    Selection.Activate
' Copy New Job No from Quotes and paste to Job Status
    Sheets("Quote Nos").Select
    Range("A200").Select
    Range("A200").Activate
    Selection.Copy
    Range("A200").Select
    Sheets("Job Status").Select
    Range("B4").Select
    Range("B4").Activate
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
    Application.ScreenUpdating = True
End Sub

Hi Graham,

Try this instead

Sub PasteStuff()
Dim lngNextRow As Long

Application.ScreenUpdating = False

    Sheets("Job Status").Select

' Unfilter sheet if required
    If ActiveSheet.FilterMode = True Then
      ActiveSheet.ShowAllData
    End If

' Add extra line into account status

    ActiveSheet.ListObjects("Table7").ListRows.Add AlwaysInsert:=True

'Get last row + 1
    lngNextRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

'Last row + 1 in Column A = cell A200 value from sheet "Quote Nos"
    Range("A" & lngNextRow) = Sheets("Quote Nos").Range("A200").Value
    
  Application.ScreenUpdating = True

End Sub

Does this work?

Pete

Thanks for your help and time Pete I will try that on the next file I have to do in a couple of days as it is not using tables so will probably be much better than below.
P.s Where does one learn this stuff as mine is all trial and error? Thanks Again.

As for the sheet in question I found after lots of trials and tribulations last night that a simple.

    Sheets("Job Status").Select

    Range("A4").Select
    Selection.AutoFilter

this removed all the auto filtering from the whole sheet and therefore by default unfiltered.
The after running rest of macro and posting new information I lastly repeated the line

   Selection.AutoFilter

Crude but does the job.

Hi Graham,

As long as it works!

As for where to learn, you've already answered the question, simply by trial and error! You'll probably spend hours agonising over something before you find the answer, but thats how we all learn.

I started learning vba from a Dummies book. It was basic stuff but got me going in the right direction by teaching the basics.

You'll spend hours searching for answers on websites and forums like this and will come across little gems and ideas that will improve your coding.  Don't worry too much about how much there is to learn, just get the basics right and the rest will follow with time.

Most importantly, have fun whilst you're doing it, if you enjoy it, you'll pick it up.

Pete