Solved

Added column to this sheet now my macro isn't working

Posted on 2012-04-03
1
174 Views
Last Modified: 2012-04-03
I added the column labled PAYPAL to the sheet called "DATABASE, now when I run my daily macro in the sheet called "DAILY SUMMARY" I am getting a debug message.  This sheet should pick up all activity from the databse sheet for the day entered in the upper left.
experts2.xls
0
Comment
Question by:wcody
[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
1 Comment
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37801767
It appears your compare date moved from column J to Column K.  See line 11 corrected.  Also, you can use .Rows.Count property rather than 65535 and then when you upgrade to newer Excel versions the number would change for max rows.  with this version, .Rows.Count would equal 65535.

See updated code:

Sub Button1_Click()
    Application.ScreenUpdating = False
 
    Dim rng As Range, cell As Range, x As String
    Dim ws As Worksheet, ws1 As Worksheet
    Set ws = Sheets("Daily Summary")
    Set ws1 = Sheets("Database")
    x = ws.Range("B1")
    If ws.Cells(4, 1).Value <> "" Then ws.Range("A4:G" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row).ClearContents
    If x <> "" Then
        Set rng = ws1.Range("k6:k" & ws1.Cells(ws.Rows.Count, "k").End(xlUp).Row)
        For Each cell In rng
            If DateValue(cell.Value) = DateValue(x) Then
                ws1.Range("A" & cell.Row & ":H" & cell.Row).Copy ws.Range("A" & ws.Cells(ws.Rows.Count, "a").End(xlUp).Row + 1)
            End If
        Next cell
    End If
    Cells.Select
    Selection.EntireColumn.AutoFit
    Range("a1").Select
    MsgBox "Done"
 
 
    Application.ScreenUpdating = True
 
End Sub

Open in new window


See attached.

Cheers,

Dave
experts2.xls
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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.
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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

635 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