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

 

[Fluff]

Is the sheet in the same workbook as the code?

 

[Tocix]

Yes, the sheet name is the same workbook.

 

[Fluff]

Ok, if you select the Caddy sheet & run this, what does it return?

Sub Check()
   With ActiveSheet
      MsgBox "|" & .Name & "|" & vbLf & Len(.Name)
   End With
End Sub

 

[Tocix]

Ok, if you select the Caddy sheet & run this, what does it return?

Sub Check()
   With ActiveSheet
      MsgBox "|" & .Name & "|" & vbLf & Len(.Name)
   End With
End Sub

 

[Fluff]

That looks Ok, what does this return

Sub Check()
   With ActiveSheet
      MsgBox .Parent.Name = ThisWorkbook.Name
   End With
End Sub

 

[Tocix]

That looks Ok, what does this return

Sub Check()
   With ActiveSheet
      MsgBox .Parent.Name = ThisWorkbook.Name
   End With
End Sub

"True"

 

[Fluff]

In that case I have no idea, as it works for me.
What happens if you use the Codename, rather than the sheet name?

 

[Tocix]

I tried the codename, but it was unsuccessful. Maybe you could assist me in creating fresh code from scratch. I have numerous sheets in a workbook. Starting on row 2, columns B, C, and D, I intended to copy and paste columns A, B, and C from the Caddy sheet to the Analysis sheet. Copy and paste the contents from columns D through G to lines L through O on the analysis sheet. Thanks a bunch for your help.

 

[jolivanes]

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