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

x
?
Solved

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

Posted on 2012-04-03
1
Medium Priority
?
186 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
1 Comment
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

971 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