1

I'm simply trying to select the third sheet within the workbook, copy three values and past them within another. The code works when I specify the exact name of the worksheet as seen below:

Sheets("Jul 01 2014 1400h").Select
        Range("U56,U64,U75").Select
        Range("U75").Activate
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Graph Data Sheet").Select
        Range("B3").Select
        ActiveSheet.Paste

But I get the error:

"Run-time error '1004': Select method of Chart class failed"

when I just want to specify the sheet order number like below:

Sheets(3).Select
        Range("U56,U64,U75").Select
        Range("U75").Activate
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Graph Data Sheet").Select
        Range("B3").Select
        ActiveSheet.Paste
Community
  • 1
  • 1
rmdlp
  • 353
  • 1
  • 4
  • 8
  • 1
    What do you mean by "does not work"? Do you get an error (if so, which error)? Or do you get unexpected behavior? – Soulfire Aug 25 '15 at 20:04
  • I get the error: "Run-time error '`004': Select method of Chart class failed" – rmdlp Aug 25 '15 at 20:06
  • @Soulfire thanks for your response! – rmdlp Aug 25 '15 at 20:07
  • 1
    A sheet in the [Sheets collection](https://msdn.microsoft.com/en-us/library/office/aa174306%28v=office.11%29.aspx) is NOT the same thing as a [Worksheet object](https://msdn.microsoft.com/en-us/library/office/aa224506(v=office.11).aspx) in the [Worksheets collection](https://msdn.microsoft.com/en-us/library/office/aa224508(v=office.11).aspx). Is there a reason you cannot refer to the worksheet by its Worksheet [.Name property](https://msdn.microsoft.com/en-us/library/office/ff196974.aspx)? –  Aug 25 '15 at 20:10
  • @Jeeped So by specifying "Sheets(3).Select" that is part of the Worksheet collection? It's just that I'm reiterating this code to obtain the values and the titles of each sheet are quite long and specific. – rmdlp Aug 25 '15 at 20:13
  • 1
    By referencing `Sheets(3)` you get the third 'sheet' (or third tab) in the sheets queue. That may or may not be a worksheet and if there was a chart sheet in sheets(1) or sheets(2) then Worksheets(3) is not the same thing as Sheets(3). –  Aug 25 '15 at 20:15
  • 1
    Relying on the [Worksheet.Index property](https://msdn.microsoft.com/en-us/library/office/ff836415.aspx) to identify the correct worksheet is usually not problematic although any reordering will foul things up. If long worksheet names are giving your typing fingers cramps, consider using the Worksheet [.CodeName property](https://msdn.microsoft.com/en-us/library/office/ff837552.aspx) instead. (and [get away from Select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) altogether) –  Aug 25 '15 at 20:19
  • 4
    I would also add that selecting, activating, etc. are unnecessary operations that take up resources. Those 8 lines of code can be condensed into one line: `Sheets(3).Range("U56,U64,U75").Copy Destination:=Sheets("Graph Data Sheet").Range("B3")` – Soulfire Aug 25 '15 at 20:23

0 Answers0