Excel VBA copy range and cells

1
votes

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
2
vbaexcel
Have you tried stepping through the code (Use F8)? My guess is you have both or the initial if statement giving a false, which ends the statement. Helps narrow down the issue, and also lets you see what values are being pulled for each variable. – Cyril
@Cyril Actually the code used to work fine when copying the entire column. I'm trying to modify it to copy only specific cells, this is where I'm getting problems. So I think the issue is with the copy/paste functions. What do you think? – aab

2 Answers

2
votes

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

0
votes

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