Solved

Help Debugging a small macro.

Posted on 2014-03-01
9
301 Views
Last Modified: 2014-03-05
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
0
Comment
Question by:RWayneH
  • 5
  • 4
9 Comments
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Please see my article on debugging. Don't be put off because it says VB6 since most of it applies to VBA.
0
 

Author Comment

by:RWayneH
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 

Author Comment

by:RWayneH
Comment Utility
? 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Sorry, I missed that. I'll get back to you.
0
 

Author Comment

by:RWayneH
Comment Utility
Thanks, the odd part about the second issue is that there is clearly 97 rows visible and not 188 as it is reporting?
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:RWayneH
Comment Utility
Thanks for the help. -R-
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now