Copy From Other Sheet, Paste to This Sheet on Activate

[ChuckRobert]

I have a Personnel Worksheet that is automatically sorted by Hire Date when the worksheet tab ("Hire Date") is clicked. This works well.

I am trying to display the same data on another sheet, sorted by last name when the worksheet tab ("Last Name") is clicked. Many other worksheets will refererence this sheet for sorted names.

I have been trying to do this by copying the data from ("Hire Date") and pasting it in ("Last Name") from View Code Activate for ("Last Name"), but I get a run-time error '1004' Select method of Range class failed for trying to copy the data from ("Hire Date").

Here is the code I am using in the worksheet tab for ("Last Name") - view code - on activate -


Private Sub Worksheet_Activate()

ActiveSheet.Unprotect Password:="password"

Cells.Select
Range("B1").Activate
Selection.Delete Shift:=xlUp
Range("F1").Select

Sheets("Hire Date").Activate
ActiveSheet.Unprotect Password:="password"
Columns("C:Q").Select
Selection.Copy

When I run by clicking the ("Last Name") tab, I get a run-time error '1004' Select method of Range class failed. The entry "Columns("C:Q").Select is in Yellow when de-bugging.

I have tried Sheets("Hire Date").Select, then activate, but I can't figure this out. Nothing is hidden - no merged cells. The copy/paste works fine if I run it from a macro, but the workbook name changes every week so I'm not sure how I could reference an Application.Run in the worksheet tab. If I replace my Tab name ("Hire Date") for the sheet name ("Sheet35") it displays "Subscript out of range".

Perhaps there is a more efficient way to do this?

 

[Von Pookie]

The code that you have posted looks fine (though you can make the code itself a little more efficient, yes). Let's work on why the code isn't working before worrying about efficient for the time being

The copy/paste works fine if I run it from a macro, but the workbook name changes every week so I'm not sure how I could reference an Application.Run in the worksheet tab.

I'm confused--how does the workbook name play into this? The code you have posted is only referring to different sheets in the same workbook which should be no problem.
I'm assuming there is more code you haven't posted?

 

[ChuckRobert]

Thanks for trying to help!

A "Save As" is completed on this program every week to a save record copy from the past week ("27 Apr 09 Schedule"). In the past, when I ran a Macro embedded in Module code, I referenced it as -
Application.Run "nameofwookbook.xls'!nameofmacro"
I'm assuming that if I try to reference a macro in the worksheet tab code on activate, it wouldn't recognize the macro next week when the workbook is renamed.

The code listed below is the first part of the macro, and it stops when trying to select a range (or even any cell) from any other worksheet. The referenced sheet is opened, but the cells are not selected (hightlighted) yet when it asks me to de-bug. It displays as such even when trying to reference an unprotected sheet.

Perhaps this is a design limitation when operating with code in the worksheet tab on activate we can work around?

I'm also interested in learning more about improving code efficiency!

 

[Von Pookie]

Oh, ok. Are you the only one using the macro? You could change it to a regular old Sub macro (instead of a Worksheet_Activate event) and put it in your Personal Macro workbook and then run it from there--it then wouldn't be referencing a specific workbook.

 

[ChuckRobert]

Thanks for the help! I used your recommendation to solve the problem.

To recap -
I was unable to drive a process to copy from another sheet from either Worksheet - View Code - On Activate, or from "ThisWorkbook".

I changed my concept and consolidated both products into 1 worksheet since the raw data was the same. I created one button to Sort by Name, and another to sort by Date of Hire. I assigned appropraite macro's to each.

Since the workbook name changes each week, I linked the buttons to the workbook modules (anyone can use), instead of specifing an Application.Run "nameofwookbook.xls'!nameofmacro".

Thanks again!