Help Debugging a small macro.

I am having issues with macro below.  There are two issues.  First is Ln31, it does not paste into the new sheet, but if I cancel and rerun it.... it works????  Why is this hanging up there the first time through? but working the second time through?

Second issue is at the end Ln51, it is placing the wrong value in that cell.  It should be 97, which is what is left as visible cell on the CDPSRECRPT sheet tab.

What am I missing?  Please advise and thanks.



Sub MOTCopyYesterdayIntoCurrentDay()
'This is used for Saturdays.  Copies Friday (Yesterday)
'into Saturdays (Today). For COOIS run on Sunday

'Goes to a sheet, clear filters, sets filter
    Sheets("CDPSRECRPT-Yesterday").Select
'resets autofilter to off if on
    If Worksheets("CDPSRECRPT-Yesterday").AutoFilterMode Then
        Selection.AutoFilter
    End If
'Turns a cleared autofiler back on
    Rows("1:1").Select
    Selection.AutoFilter
'filter for tody -1
    ActiveSheet.UsedRange.AutoFilter Field:=16, _
    Criteria1:=Format(Date - 1, "m/d/yyyy"), Operator:=xlFilterValues
'Copies what is visible in the autofilter, excludes headers.
    Set UsdRng = Sheets("CDPSRECRPT-Yesterday").UsedRange
    Set myRng = UsdRng.Offset(1).Resize(UsdRng.Rows.Count - 1, UsdRng.Columns.Count)
'    myRng.Columns(1).SpecialCells(xlVisible).Copy   'just copys column A or 1
     myRng.Columns.SpecialCells(xlVisible).Copy
'goes to destination sheet
    Sheets("CDPSRECRPT").Select
'resets autofilter to off if on
    If Worksheets("CDPSRECRPT").AutoFilterMode Then
        Selection.AutoFilter
    End If
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select 'select one more row down
    ActiveSheet.Paste
    
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
'set filter for today -1
    ActiveSheet.UsedRange.AutoFilter Field:=16, _
    Criteria1:=Format(Date - 1, "m/d/yyyy"), Operator:=xlFilterValues
    
    Columns("A:A").Select
    ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, _
        6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33 _
        , 34, 35, 36), Header:=xlYes
    Range("A1").Select
    Sheets("CDPSRECRPT").Select
    RowCnt3 = ActiveSheet.UsedRange.Columns(1).SpecialCells(xlVisible).Count - 1
    
    Sheets("MOT-MeasureData").Select

    Range("B2").Select
    Range("B2") = RowCnt3
'replaces the number for yesterday in MOT-MeasureData
'with the new number.

'    Sheets("ControlPage").Select

    
End Sub

Open in new window

MOTBook13.xlsm
RWayneHAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Please see my article on debugging. Don't be put off because it says VB6 since most of it applies to VBA.
0
RWayneHAuthor Commented:
I read this article and it is not helping.  I believe I am losing what is copied and it does not know what to paste for the first issue, but it works fine when I rerun it.  In the second issue it is not counting the visible rows correctly.  I am using this is other subs and it works fine.

Need some to look at the code.  What edits could you recomend?
0
Martin LissOlder than dirtCommented:
In order for me to help I will need you to attach your workbook. I would also need a list of steps to take so I can reproduce your problem.
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

RWayneHAuthor Commented:
? Workbook is attached in original question.  Open workbook and run the only macro that is in it.  It will fail.  End the debug, run it again and it works.   Leads to issue two.
0
Martin LissOlder than dirtCommented:
Sorry, I missed that. I'll get back to you.
0
RWayneHAuthor Commented:
Thanks, the odd part about the second issue is that there is clearly 97 rows visible and not 188 as it is reporting?
0
Martin LissOlder than dirtCommented:
OK here is the corrected code. I've marked the four changes I made with 'changed. Two of the changes involved the formatting of the date it was filtering on. I had to make that change because for me in Excel 2010 it found no records unless I did.

Sub MOTCopyYesterdayIntoCurrentDay()
'This is used for Saturdays.  Copies Friday (Yesterday)
'into Saturdays (Today). For COOIS run on Sunday

'Goes to a sheet, clear filters, sets filter
    Sheets("CDPSRECRPT-Yesterday").Select
'resets autofilter to off if on
    If Worksheets("CDPSRECRPT-Yesterday").AutoFilterMode Then
        Selection.AutoFilter
    End If
'Turns a cleared autofiler back on
    Rows("1:1").Select
    Selection.AutoFilter
'filter for tody -1
'changed
'    ActiveSheet.UsedRange.AutoFilter Field:=16, _
'    Criteria1:=Format(Date - 1, "m/d/yyyy"), Operator:=xlFilterValues
    ActiveSheet.UsedRange.AutoFilter Field:=16, _
    Criteria1:=Format(Date - 1, "mm-dd-yyyy"), Operator:=xlFilterValues

'Copies what is visible in the autofilter, excludes headers.
    Set UsdRng = Sheets("CDPSRECRPT-Yesterday").UsedRange
    Set myRng = UsdRng.Offset(1).Resize(UsdRng.Rows.Count - 1, UsdRng.Columns.Count)
'    myRng.Columns(1).SpecialCells(xlVisible).Copy   'just copys column A or 1
     myRng.Columns.SpecialCells(xlVisible).Copy
'goes to destination sheet
    Sheets("CDPSRECRPT").Select
'resets autofilter to off if on
'   changed
'    If Worksheets("CDPSRECRPT").AutoFilterMode Then
'        Selection.AutoFilter
'    End If

    Range("A1").Select

    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select 'select one more row down
    ActiveSheet.Paste
    
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
'set filter for today -1
'Changed
'    ActiveSheet.UsedRange.AutoFilter Field:=16, _
'    Criteria1:=Format(Date - 1, "m/d/yyyy"), Operator:=xlFilterValues
    ActiveSheet.UsedRange.AutoFilter Field:=16, _
    Criteria1:=Format(Date - 1, "mm-dd-yyyy"), Operator:=xlFilterValues

    
    Columns("A:A").Select
    ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, _
        6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33 _
        , 34, 35, 36), Header:=xlYes
    Range("A1").Select
    Sheets("CDPSRECRPT").Select
    'changed
'    RowCnt3 = ActiveSheet.UsedRange.Columns(1).SpecialCells(xlVisible).Count - 1
    Dim rng As Range
    Set rng = ActiveSheet.AutoFilter.Range
    RowCnt3 = rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1

    Sheets("MOT-MeasureData").Select

    Range("B2").Select
    Range("B2") = RowCnt3
'replaces the number for yesterday in MOT-MeasureData
'with the new number.

'    Sheets("ControlPage").Select

    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RWayneHAuthor Commented:
Thanks for the help. -R-
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2013
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.