Sheet.Activate versus Sheet.Select

[shawnrpg]

I can't find a good answer via searching (maybe I'm asking the wrong way).

When switching from one worsheet to another worksheet (within the same workbook), which is the preferred/correct way to switch,
Sheets("sheet name").Select or
Sheets("sheet name").Activate?

I am using Excel 2007.

Thanks,

Shawn

 

[xenou]

You've gone too far with chaining objects together. Open the workbook, then copy from it:

Sub Copy_Data_Without_Selecting()
Dim wb As Workbook
    Set wb = Workbooks.Open Filename:="C:\[I]...Folders...[/I]\End Use Programs.xlsx"
    wb.Sheets("End Use Programs").Range("A1:F5000").Copy _
            Destination:=Workbooks("Book3.xlsx").Sheets("Sheet2").Range("A1")
End Sub

 

[shawnrpg]

I tried the revised code and changed the path for the actual location but got a Compile Error: Expected:End of Statement. I finally figured out that I needed to add parenthasis around Filename and the end of the line.

Then I get a Run-time error '9': Subscript out of range when it gets to the wbSheets line of code. The Help file wasn't much help with this.

Any thoughts?

Shawn

 

[VoG]

This should work (with the full path filled in)

Sub Copy_Data_Without_Selecting()
Dim wb As Workbook
    Set wb = Workbooks.Open(Filename:="C:\...Folders...\End Use Programs.xlsx")
    wb.Sheets("End Use Programs").Range("A1:F5000").Copy _
            Destination:=Workbooks("Book3.xlsx").Sheets("Sheet2").Range("A1")
End Sub

Runtime error 9 means that one or more of "End Use Programs", "Book3.xlsx", "Sheet2" doesn't exist (or Book3.xlsx is not open).

 

[shawnrpg]

No; I made sure I changed the filename path and workbook/worksheet names were changed to reflect the actual [test] files I am using.

I just recopied the code, change the path and workbook name (Book2 in my test today; book3 just happened to be the workbook name I was using yesterday), and made sure the sheet name was correct.
I am still getting the Run-time error '9'.

For this test, I am using a blank 3-tab workbook (Book2), the End Use Programs.xlsx file and the code in question. I have no other code since I am trying to get this piece to work. Once (if) I get this piece working, I will incorporate it into the actual file.

Shawn

 

[VoG]

Something isn't open or doesn't exist. Check for additional leading/trailing/embedded spaces in the actual tab names.

 

[schielrn]

I assume the sheet "End Use Programs" exists in the file with the exact same name? Make sure each of the sheets you are trying to reference exist in the correct workbooks.

 

[shawnrpg]

I double checked and there are no leading or trailing spaces/characters in the sheet names; there is only 1 space between words in sheet names (where a sheet name is something other than Sheet1, Sheet2, etc.).

There are no leading or trailing spaces/characters in the workbook names.

The sheet, End Use Programs, as an actual sheet tab name in the End Use Programs.xlsx workbook.

Here is the actual code I am using (with the actual filename path, workbooks, and sheets):

Sub CopyData()
Dim wb As Workbook
Set wb = Workbooks.Open(Filename:="Q:\AIP\Teams\Estimating\Oracle System\End Use Programs.xlsx")
wb.Sheets("End Use Programs").Range("A1:F5000").Copy Destination:=Workbooks("Book2.xlsx").Sheets("Sheet2").Range("A1")
End Sub

Shawn

 

[xenou]

Do it by hand while recording a macro.
Something is amiss but who knows. maybe the recorded code will provide a clue.

 

[shawnrpg]

I'll give it a shot but I'll bet it will give me code similar to what I'm using now where .Activate is used to switch workbooks.

I'll post my finding.

Shawn

 

[shawnrpg]

Here's the code that was generated when I recorded the macro:

Sub CopyDataTest()
    Workbooks.Open Filename:= _
        "Q:\AIP\Teams\Estimating\Oracle System\End Use Programs.xlsx"
    Range("A1:F5000").Select
    Selection.Copy
    Windows("Book2").Activate
    Sheets("Sheet2").Select
    ActiveSheet.Paste
End Sub

I know I can replace the Range("A1:F5000").Select with Range("A1:F5000").Copy (to eliminate the .Select and Selection.Copy), but the whole point of my original question was to copy data without activating (switching) workbooks and sheets.

Shawn