Copy Worksheet to another workbook, programatically?

[plh]

Hi Everyone,
What I need to do is programatically copy a worksheet from a workbook (call it
Book1) to another (call it Book2), renaming the Worksheet before it goes into
Book2. The initial condition being that Book2 is closed. I thought I had a way
but I keep running into various problems. I'm not reprinting that here because I
think I need to start fresh. My question is, what is the cleanest way to do it?
Thank You all so much in advance,
-plh

I keep hitting "Esc", but I'm still here!

 

[Tom Ogilvy]

Given Bob's valuable information:

' assume book1.xls is open, book2.xls may be open or not
' assume not default books, but books with these names,
' previously saved

Sub Tester1()
Dim bOpen
Dim bk1 As Workbook
Dim bk2 As Workbook
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Application.ScreenUpdating = False
Set bk1 = Workbooks("Book1.xls")
Set sh1 = bk1.Worksheets("Sheet1")
On Error Resume Next
Set bk2 = Workbooks("Book2.xls")
bOpen = True
On Error GoTo 0
If bk2 Is Nothing Then
Set bk2 = Workbooks.Open("C:\My Folder\Book2.xls")
End If
sh1.Copy After:=bk2.Worksheets(bk2.Worksheets.Count)
Set sh2 = bk2.Worksheets(bk2.Worksheets.Count)
' or set sh2 = Activesheet
sh2.Name = "ABCD"
If Not bOpen Then
bk2.Close SaveChanges:=True
Else
bk2.Save
End If
Application.ScreenUpdating = True
End Sub

Code untested and may contain typos.

 

[Bob Phillips]

Mr Ogilvy,

From his post he seemed relatively able, just wanted to know how to do
something that was not possible. I sought to give him enough information to
stop him wasting his time on that pursuit.

 

[Tom Ogilvy]

Apparently you misinterpreted my statement and are trying to turn it into
sometype of affront.

I said
"Given Bob's valuable information: "
and you seem to interpret it as some kind of sarcasm - which was not
intended. I suspect 99.9% of people reading that would take no offense.
While it isn't worth the search, if you did, you would find I often use such
terminology to acknowledge someone else's post to indicate I have read it
and agree. This is the first time someone has turned it into an insult.

Why not get a positive outlook on life and quit badgering people with
pettiness. The purpose of the group is technical discussion and assistance.

the OP clearly stated:

I thought I had a way
but I keep running into various problems. I'm not reprinting that here because I
think I need to start fresh. My question is, what is the cleanest way to do

it?

so given your response (which I acknowledged), I provided one approach,
illustrating your suggestion, to work around the problem (which would appear
to the user that book2.xls had not been opened). If you felt the OP didn't
want sample code, your welcome to your opinion. Don't try to impose it on
me.

 

[plh]

Gentlemen! Please!
Tom's code worked swimmingly except I had to change:

sh1.Copy After:=bk2.Worksheets(bk2.Worksheets.Count)

to

Set shTo = ActiveSheet
shFrom.Copy After:=bkTo.ActiveSheet
'shFrom.Copy After:=bkTo.Worksheets.Count
Set shTo = ActiveSheet
shTo.Name = strNewSheetName

where the commented line is the application specific version of his original
line. The original version I generated Error 1004 (I forget which exact flavor,
application derived, something like that).
(One needs the repetition of: "Set shTo = ActiveSheet" or it renames the sheet
that the inserted one is copied after, rather than the new one.)

Thank you again so much, this group is a life-saver!
-plh

Given Bob's valuable information:

' assume book1.xls is open, book2.xls may be open or not
' assume not default books, but books with these names,
' previously saved

Sub Tester1()
Dim bOpen
Dim bk1 As Workbook
Dim bk2 As Workbook
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Application.ScreenUpdating = False
Set bk1 = Workbooks("Book1.xls")
Set sh1 = bk1.Worksheets("Sheet1")
On Error Resume Next
Set bk2 = Workbooks("Book2.xls")
bOpen = True
On Error GoTo 0
If bk2 Is Nothing Then
Set bk2 = Workbooks.Open("C:\My Folder\Book2.xls")
End If
sh1.Copy After:=bk2.Worksheets(bk2.Worksheets.Count)
Set sh2 = bk2.Worksheets(bk2.Worksheets.Count)
' or set sh2 = Activesheet
sh2.Name = "ABCD"
If Not bOpen Then
bk2.Close SaveChanges:=True
Else
bk2.Save
End If
Application.ScreenUpdating = True
End Sub

Code untested and may contain typos.

I keep hitting "Esc", but I'm still here!

 

[plh]

Aach! Don't tell me! I just noticed! :-{0
-plh

Gentlemen! Please!
Tom's code worked swimmingly except I had to change:


to

Set shTo = ActiveSheet
shFrom.Copy After:=bkTo.ActiveSheet
'shFrom.Copy After:=bkTo.Worksheets.Count
Set shTo = ActiveSheet
shTo.Name = strNewSheetName

where the commented line is the application specific version of his original
line. The original version I generated Error 1004 (I forget which exact flavor,
application derived, something like that).
(One needs the repetition of: "Set shTo = ActiveSheet" or it renames the sheet
that the inserted one is copied after, rather than the new one.)

Thank you again so much, this group is a life-saver!
-plh



I keep hitting "Esc", but I'm still here!

I keep hitting "Esc", but I'm still here!

 

[smokiibear]

Tom...i do not understand the following if statement:

If Not bOpen Then
bk2.Close SaveChanges:=True
Else
bk2.Save
End If


as far as i can tell, bopen will always be true, right?

Smokii

 

[smokiibear]

Tom...i do not understand the following if statement:

If Not bOpen Then
bk2.Close SaveChanges:=True
Else
bk2.Save
End If


as far as i can tell, bopen will always be true, right?

Smokii