Copy From Other Sheet, Paste to This Sheet on Activate

ChuckRobert

Board Regular
Joined
Feb 26, 2009
Messages
64
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?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

Board Regular
Joined
Feb 26, 2009
Messages
64
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

Board Regular
Joined
Feb 26, 2009
Messages
64
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,262
Messages
5,769,105
Members
425,518
Latest member
seothaeng

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top