Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help Debugging a small macro.

Posted on 2014-03-01
9
Medium Priority
?
313 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 49

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 49

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 49

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 49

Accepted Solution

by:
Martin Liss earned 2000 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 49

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

722 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