Excel VBA code that copies data from one sheet into another

Question

I'm trying to achieve the following:

Take a record of the busiest hour of each day, (i.e highest number of customers) and record its details (number of customers, amount spent, average time spent) into the second spreadsheet.

On the second spreadsheet, a new column will be added everytime. It will have records of only the busiest hour of each day.

Sub DailySales()
    Dim dailySht As Worksheet 'worksheet storing latest store activity
    Dim recordSht As Worksheet 'worksheet to store the highest period of each Day
    Dim lColDaily As Integer ' Last column of data in the store activity sheet
    Dim lCol As Integer ' Last column of data in the record sheet
    Dim maxCustomerRng As Range ' Cell containing the highest number of customers
    Dim maxCustomerCnt As Long ' value of highest customer count

    Set dailySht = ThisWorkbook.Sheets("Supermarket Data")

    Set recordSht = ThisWorkbook.Sheets("Record Data")
    With recordSht
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).column
    End With
    With dailySht
        lColDaily = .Cells(1, .Columns.Count).End(xlToLeft).column
        maxCustomerCnt = Application.Max(.Range(Cells(2, 1), Cells(2, lColDaily)))
        Set maxCustomerRng = .Range(.Cells(2, 1), .Cells(2, lColDaily)).Find(What:=maxCustomerCnt, LookIn:=xlValues)
        If Not maxCustomerRng Is Nothing Then
            maxCustomerRng.EntireColumn.Copy recordSht.Cells(1, lCol + 1)
        End If
    End With

    Set maxCustomerRng = Nothing
    Set dailySht = Nothing
    Set recordSht = Nothing
End Sub

I get a "run-time error '1004': Method 'Range' of object'_Worksheet failed" on the following line everytime I run the code (it is attached to a button on the second worksheet).

maxCustomerCnt = Application.Max(.Range(Cells(2, 1), Cells(2, lColDaily)))

This is the table:

Customer data   7:00:00 AM  7:30:00 AM  8:00:00 AM  8:30:00 AM  9:00:00 AM  
Number of customers 33         37         110          250        84
Amount spent        65         50          70           85        60
Average time spent  12         10           8           17        10

Can someone please help me figure out whats wrong with the code?

Mrig · Accepted Answer · 2017-08-23 09:48:33Z

4

Instead of

.Range(Cells(2, 1), Cells(2, lColDaily))

try

.Range(.Cells(2, 1), .Cells(2, lColDaily))

answered Aug 23, 2017 at 9:48

Mrig

11.7k2 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

aab Over a year ago

That was it. Thanks. Is there a way to prevent data duplication? I.e. not record more than one data for a single day?

Mrig Over a year ago

@aab - That's completely different question all together. I suggest you to kindly close the question by accepting answer that helped you and then ask a new question. New question will also attract more people rather than just two of us here.

Mrig Over a year ago

@aab - Kindly mention the link of your new question here so that I can have a look too.

aab Over a year ago

here's a link to my new question stackoverflow.com/questions/45840040/…

FunThomas · Accepted Answer · 2017-08-23 09:58:25Z

1

Mrigs comment is right, use .Range(.Cells(2, 1), .Cells(2, lColDaily))

Just as explanation (as the error is so common): cells (without leading dot) refers to the active sheet. .cells (with dot) in your case refers to the sheet of the with-statement, you could write dailySht.cells instead.

So in your statement, you ask for a Range of the worksheet dailySht that is defined by cells from the active sheet (which is very likely a different sheet), which is not possible and raises the error you see.

answered Aug 23, 2017 at 9:58

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

1 Comment

aab Over a year ago

I didn't know that. Thanks for the explanation.

Collectives™ on Stack Overflow

Excel VBA code that copies data from one sheet into another

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related