VBA Macro code fail; Copying and Pasting from range into new range

Question

I've searched but can't find the answer to this specific problem I'm having. I am trying to copy a range of data from one worksheet onto a different worksheet after identifying the cell value on the worksheet I'm PASTING TO by the date that matches a cell on the COPY FROM worksheet. Below is my code. The macro works when I run it from the PASTE TO worksheet ('Daily Summary Record') but does not work if I run it from another worksheet. I want to be able to run it from any sheet in the workbook, but especially from the PASTE FROM worksheet. See attachments for images of the two worksheets.

'Daily Itemized')

Sub ArchiveWeek()

Set thisMon = Worksheets("Daily Itemized").Range("F5") 'Assigns variable thisMon as the date value in Daily Itemized Tab, F5 cell

Dim ws As Excel.Worksheet
Dim FoundCell As Excel.Range

Set ws = Worksheets("Daily Summary Record")
Set FoundCell = ws.Range("D:D").Find(what:=thisMon, lookat:=xlWhole)
If Not FoundCell Is Nothing Then
    FoundCell.Offset(0, 1).Select 'PROBLEM. Supposed to: Selects the cell to the adjacent right of the cell in column D with the same date as the Itemized F5 cell
    Worksheets("Daily Itemized").Range("G5:S11").Copy  'Works to copy range on Daily sheet
    FoundCell.Offset(0, 1).Select 'reselects the cell to right of FoundCell
    Selection.PasteSpecial xlPasteValues  'works!
    MsgBox ("Your week time values have been pasted!")
Else
    MsgBox ("The Date of " & thisMon & " was not found in the Daily Summary Record, Column D. Recheck values.")
End If

End Sub

'Daily Itemized' 'Daily Summary Record'

I should add, I'm a very novice VBA coder. I've only really just expanded past using the macro recorder to writing original code, so baby steps are appropriate...And I'm entirely self-taught. — mbahonen
When I tried option #2 I got a debug error on this line of code: — mbahonen
sorry, it won't let me edit my last comment. This line of code: FoundCell.Offset(0, 1).Resize(7, 13).Values = _ Worksheets("Daily Itemized").Range("G5:S11").Values — mbahonen

YowE3K YowE3K · Accepted Answer · 2016-12-30T00:40:03

Avoid using Select (and Selection and Activate) wherever possible:

Sub ArchiveWeek()
    Set thisMon = Worksheets("Daily Itemized").Range("F5") 'Assigns variable thisMon as the date value in Daily Itemized Tab, F5 cell

    Dim ws As Excel.Worksheet
    Dim FoundCell As Excel.Range

    Set ws = Worksheets("Daily Summary Record")
    Set FoundCell = ws.Range("D:D").Find(what:=thisMon, lookat:=xlWhole)
    If Not FoundCell Is Nothing Then
        'Copy range on Daily sheet
        Worksheets("Daily Itemized").Range("G5:S11").Copy
        'Paste it on the summary sheet commencing one cell
        ' to the right of the location of the date
        FoundCell.Offset(0, 1).PasteSpecial xlPasteValues
        MsgBox ("Your week time values have been pasted!")
    Else
        MsgBox ("The Date of " & thisMon & " was not found in the Daily Summary Record, Column D. Recheck values.")
    End If

Also, because you are only wanting to copy values, you could improve your code by by-passing the clipboard (with all the associated risks of the user copying something else to the clipboard between when your code executes the Copy and when it executes the Paste) and just setting the Values in the destination area to be the Values in the source area:

Sub ArchiveWeek()
    Set thisMon = Worksheets("Daily Itemized").Range("F5") 'Assigns variable thisMon as the date value in Daily Itemized Tab, F5 cell

    Dim ws As Excel.Worksheet
    Dim FoundCell As Excel.Range

    Set ws = Worksheets("Daily Summary Record")
    Set FoundCell = ws.Range("D:D").Find(what:=thisMon, lookat:=xlWhole)
    If Not FoundCell Is Nothing Then
        'Copy values from Daily sheet to Summary sheet, commencing
        ' one cell to the right of the location of the date
        FoundCell.Offset(0, 1).Resize(7, 13).Value = _
                    Worksheets("Daily Itemized").Range("G5:S11").Value
        MsgBox ("Your week time values have been pasted!")
    Else
        MsgBox ("The Date of " & thisMon & " was not found in the Daily Summary Record, Column D. Recheck values.")
    End If

VBA Macro code fail; Copying and Pasting from range into new range

2 Answers