PDA

View Full Version : Solved: Sending Copy of WorkSheet from 1 Book to Another



Saladsamurai
09-29-2009, 08:05 AM
Here is the deal. I have one book called "Data_ENTRY" and another book called "Data_ANALYSIS."
In both books, I have a WorkSheet named "Cooling Type" with the same data set.

As the names of the books suggest, I ENTER new data into the Data_Entry book in the next available rows. Then I want to send a COPY of "Cooling Type" from Data_ENTRY to Data_ANALYSIS.



Here's the clencher: I want the copy of "Cooling Type" from Data_ENTRY to REPLACE the one in Data_Analysis. And I need its name to remain "Cooling Type"

As you may know, if you manually copy a Worksheet from one book to another it renames it, e.g. "Cooling Type (2)" and does not replace the existing sheet in the destination book.

I also need the original "Cooling Type" worksheet to remain in Data_ENTRY.


Any ideas? I will eventually like to apply to multiple sheets...maybe as a Click_Button ().

Saladsamurai
09-29-2009, 08:22 AM
I just did the follwing with 2 sample workbooks:

I sent a copy manually from ENTRY to ANALYSIS. Deleted the previously existing Cooling Type in ANALYSIS.

And renamed "Cooling Type (2)" to "Cooling Type"

This was the Macro that generated:

Sub WorkSheetCOPY_test()
'
' WorkSheetCOPY_test Macro
'
'
'
Sheets("Cooling Type").Select
Sheets("Cooling Type").Copy Before:=Workbooks("DATA_ANALYSIS_SAMPLE.xls"). _
Sheets(1)
Sheets("Cooling Type").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Cooling Type (2)").Select
Sheets("Cooling Type (2)").Name = "Cooling Type"
End Sub

I am just a little confused by this line

Sheets("Cooling Type").Copy Before:=Workbooks("DATA_ANALYSIS_SAMPLE.xls"). _
Sheets(1)


Can someone explain? Why does it all of sudden switch to the Sheets(1) reference style?

EDIT: I think I get it. It is saying: Make a Copy of Sheets("Cooling Type") before we send it to
Workbooks("DATA_ANALYSIS_SAMPLE.xls") as the 1st sheet in Workbooks("DATA_ANALYSIS_SAMPLE.xls")

right?

Saladsamurai
09-29-2009, 08:57 AM
Alright. It does not look like there is much to 'clean out' of this Macro.

I thought that I could get rid of the .Selects but when I do that, it does not work properly.

Don't .Selects slow things down? Should I be doing something with ScreenUpdating so that
I don't actually see what it going on?

Saladsamurai
09-29-2009, 09:08 AM
Arrrgggg.... when I run the code, it still prompts me:

"Sheet contains data...Are you sure you want to delete?"

The Macro does not record how the YES response is sent to Excel. How do I beat this?

Saladsamurai
09-29-2009, 09:20 AM
For Anyone who is interested in how I solved this one, I added the line in bold to kill the prompt:

Sub WorkSheetCOPY_test()
Application.ScreenUpdating = False

Sheets("Cooling Type").Select
Sheets("Cooling Type").Copy Before:=Workbooks("DATA_ANALYSIS_SAMPLE.xls"). _
Sheets(1)
Sheets("Cooling Type").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheets("Cooling Type (2)").Select
Sheets("Cooling Type (2)").Name = "Cooling Type"

Application.ScreenUpdating = True
End Sub



EDIT: BY the way, this solution fails miserably in the grand scheme of things.. I thought by renaming the worksheets with the same names, my other worksheets' worksheet functions would stay happy.

They're not. They all say 'REF'


...BS

mdmackillop
09-29-2009, 04:16 PM
When working with more than one book, it is always better to qualify sheets with book references to avoid confusion. No need to Select anything!

Move is simpler than Copy here


Sub WorkSheetCOPY_test()
Dim WB As Workbook
Set WB = ActiveWorkbook
WB.Sheets("CT").Move Before:=Workbooks("DAS.xls").Sheets(1)
'WB.Activate 'optional
WB.Sheets("CT (2)").Name = "CT"
Application.ScreenUpdating = True
End Sub