Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help Debugging a small macro.

Posted on 2014-03-01
9
Medium Priority
?
314 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

876 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