Excel VBA copy range and cells

Question

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

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, Commented Oct 16, 2017 at 13:52
@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
– aab, Commented Oct 16, 2017 at 14:09

Harassed Dad · Accepted Answer · 2017-10-16 14:00:35Z

2

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

answered Oct 16, 2017 at 14:00

Harassed Dad

4,7141 gold badge12 silver badges12 bronze badges

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

1 Comment

aab Over a year ago

I think the issue is with the copy and paste functions. The code was working fine when copying the entire column instead of specific cells.

Mathieu Guindon · Accepted Answer · 2017-10-16 14:14:24Z

0

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

answered Oct 16, 2017 at 14:14

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

1 Comment

aab Over a year ago

Thanks but the code is linked to a button on the recordSht button so I guessing that shouldn't be an issue?

Collectives™ on Stack Overflow

Excel VBA copy range and cells

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related