Solved

Help Debugging a small macro.

Posted on 2014-03-01
9
307 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 46

Expert Comment

by:Martin Liss
ID: 39897613
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
ID: 39897660
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 46

Expert Comment

by:Martin Liss
ID: 39897679
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:RWayneH
ID: 39897682
? 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
 
LVL 46

Expert Comment

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

Author Comment

by:RWayneH
ID: 39897691
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 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 39897786
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
ID: 39906669
Thanks for the help. -R-
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39906847
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

809 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