VBA code cannot find sheet name

[Tocix]

Good morning,

I'm trying to get a VBA code that will copy data from sheet Caddy and paste it to destination sheet Analysis. Copy columns A through C from sheet Caddy and paste them to Analysis B through D, starting on row 2. And column D through G, and paste it to L through O. But I'm getting run time error 9 on "Set wsCaddy= ThisWorkbook.Sheets ("Caddy"). I do have the sheet name "Caddy" on the last tab.

    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim lastRow As Long

    ' Set references to the source and destination sheets
[COLOR=rgb(226, 80, 65)]    Set wsSource = ThisWorkbook.Sheets("Caddy")[/COLOR]
    Set wsDest = ThisWorkbook.Sheets("Analysis")

    ' Determine the last row with data in column A on the source sheet
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row

    ' Copy columns A through C from the source sheet
    wsSource.Range("A1:C" & lastRow).Copy
    ' Paste the copied data into the destination sheet starting at row 2, column B
    wsDest.Range("B2").PasteSpecial Paste:=xlPasteValues

    ' Copy columns D through G from the source sheet
    wsSource.Range("D1:G" & lastRow).Copy
    ' Paste the copied data into the destination sheet starting at row 2, column L
    wsDest.Range("L2").PasteSpecial Paste:=xlPasteValues

    ' Clear Clipboard to remove marching ants
    Application.CutCopyMode = False
End Sub

 

[Tocix]

You said the your "Caddy" sheet is the last sheet in the workbook.
Try it so.

Sub Maybe()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim lr As Long
Set sh1 = ThisWorkbook.Worksheets("Analysis")
Set sh2 = Sheets(ThisWorkbook.Worksheets.Count)
lr = sh2.Cells(sh2.Rows.Count, 1).End(xlUp).Row
    With sh1
        .Cells(2, 2).Resize(lr, 3).Value = sh2.Cells(1, 1).Resize(lr, 3).Value
        .Cells(2, 12).Resize(lr, 4).Value = sh2.Cells(1, 4).Resize(lr, 4).Value
    End With
End Sub

OMG!! Thank you so much for your help; it works perfectly. For the life of me, I couldn't figure out why it worked a couple of days ago and didn't today.

 

[jolivanes]

Thanks for the update and good luck.
Still would like to know why getting to the "Caddy" sheet with the other macros did not work.
If you type the Word Caddy into a cell, select the cell, Ctrl+Insert, double click on the Caddy Tab and Shift+Insert, can you use it then with the name in the code?