CODEWITHSUNDEEP
vbaexcel
aab
aab

Reputation: 1739

Excel VBA copy range and cells

I'm trying to copy data from Sheet A into Sheet B.

I want to copy a range of cells from row 4 to row 13 of column X (X=column of cell with the highest value of row 13, and paste the copied values into row 4 to row 13 of sheet B.

Running the code does not copy the data, I get no errors but nothing gets pasted.

Can someone please take a look at the code to see where my mistake is:

Sub Daily()
    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 CheckForDups As Range ' Used to find duplicate dates on the record Sheet
    Dim maxCustomerCnt As Double ' value of highest customer count



    Set dailySht = ThisWorkbook.Sheets("Sheet A")
    Set recordSht = ThisWorkbook.Sheets("Sheet B")
    With recordSht
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).column
    End With
    With dailySht
        lColDaily = .Cells(1, .Columns.Count).End(xlToLeft).column
        maxCustomerCnt = Round(Application.Max(.Range(.Cells(13, 1), .Cells(13, lColDaily))), 2)
        Set maxCustomerRng = .Range(.Cells(13, 1), .Cells(13, lColDaily)).Find(What:=maxCustomerCnt, LookIn:=xlValues)
        If Not maxCustomerRng Is Nothing Then
        ' Check the Record Sheet to ensure the data is not already there
            Set CheckForDups = recordSht.Range(recordSht.Cells(13, 1), recordSht.Cells(13, lCol)).Find(What:=Round(maxCustomerRng.Value, 2), LookIn:=xlValues)
        ' If CheckForDups is Nothing then the date was not found on the record sheet. Therefore, copy the column
            If CheckForDups Is Nothing Then
                Range(Cells(4, maxCustomerRng), Cells(13, maxCustomerRng)).Copy
                recordSht.Cells(4, lCol + 1).PasteSpecial xlPasteValues
                recordSht.Cells(4, lCol + 1).PasteSpecial xlPasteFormats
            End If
        End If
    End With

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

Upvotes: 1

Views: 1482

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

You have this:

With dailySht

And then these implicit references to the ActiveSheet within that With block:

Range(Cells(4, maxCustomerRng), Cells(13, maxCustomerRng)).Copy

These implicit ActiveSheet references make your code work depending on which worksheet is currently active, which is likely not what you intended - FWIW Rubberduck (an open-source VBIDE add-in project I manage) can help you easily find them everywhere they are in your project:

Rubberduck inspections

The solution is likely to qualify these calls with a dot, so that they work off the dailySht worksheet object:

.Range(.Cells(4, maxCustomerRng), .Cells(13, maxCustomerRng)).Copy

Upvotes: 0

Harassed Dad
Harassed Dad

Reputation: 4704

You're trying to find a rounded value in a set of unrounded data. CheckForDups is always nothing

Upvotes: 2

Related Questions