Moving Sheets To A Different Workbook XL2003
 

Hi,

XL 2003, Windows XP.

I have a question regarding moving sheets to a different workbook.

Sub Blah()

Dim oActiveSheet As Object

blah blah blah

Sheets("Duplicate").Copy Befo=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet

oActiveSheet.Move
after:=Workbooks("2006.05.30Presentation.xls").She ets(Sheets.Count)

End Sub

On the last statement, I get a Run-time error '9': Subscript out of range.

Where did I go wrong? The other workbooks is named
"2006.05.30Presentation.xls". I want to move oActiveSheet from WorkbookA to
2006.05.30Presentation.xls at the very end.


Thank you.

Best regards,
Kevin

Moving Sheets To A Different Workbook XL2003
 

I did the below mopd and your code worked

Sub Blah()

Dim oActiveSheet As Object
Dim x As Long

Sheets("Duplicate").Copy Befo=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet
x = Workbooks("2006.05.30Presentation.xls").Sheets.Cou nt
oActiveSheet.Move after:=Workbooks("2006.05.30Presentation.xls").She ets(x)

End Sub

--
Tony Green


"Kevin H. Stecyk" wrote:

Hi,

XL 2003, Windows XP.

I have a question regarding moving sheets to a different workbook.

Sub Blah()

Dim oActiveSheet As Object

blah blah blah

Sheets("Duplicate").Copy Befo=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet

oActiveSheet.Move
after:=Workbooks("2006.05.30Presentation.xls").She ets(Sheets.Count)

End Sub

On the last statement, I get a Run-time error '9': Subscript out of range.

Where did I go wrong? The other workbooks is named
"2006.05.30Presentation.xls". I want to move oActiveSheet from WorkbookA to
2006.05.30Presentation.xls at the very end.


Thank you.

Best regards,
Kevin

Moving Sheets To A Different Workbook XL2003
 

the unqualified Sheets.count refers to the active workbook try it this way

Sub Kevin2()
Dim oActiveSheet As Object



Sheets("Duplicate").Copy Befo=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet

With Workbooks("2006.05.30Presentation.xls")
oActiveSheet.Move after:=.Sheets(.Sheets.Count)
End With
End Sub


--
Regards,
Tom Ogilvy


"Kevin H. Stecyk" wrote:

Hi,

XL 2003, Windows XP.

I have a question regarding moving sheets to a different workbook.

Sub Blah()

Dim oActiveSheet As Object

blah blah blah

Sheets("Duplicate").Copy Befo=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet

oActiveSheet.Move
after:=Workbooks("2006.05.30Presentation.xls").She ets(Sheets.Count)

End Sub

On the last statement, I get a Run-time error '9': Subscript out of range.

Where did I go wrong? The other workbooks is named
"2006.05.30Presentation.xls". I want to move oActiveSheet from WorkbookA to
2006.05.30Presentation.xls at the very end.


Thank you.

Best regards,
Kevin

Moving Sheets To A Different Workbook XL2003
 

ADG wrote...
I did the below mopd and your code worked

Sub Blah()

Dim oActiveSheet As Object
Dim x As Long

Sheets("Duplicate").Copy Befo=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet
x = Workbooks("2006.05.30Presentation.xls").Sheets.Cou nt
oActiveSheet.Move
after:=Workbooks("2006.05.30Presentation.xls").She ets(x)

End Sub

--
Tony Green



Thank you Tony. I see my error.

Best regards,
Kevin

Moving Sheets To A Different Workbook XL2003
 

Tom Ogilvy wrote...

the unqualified Sheets.count refers to the active workbook try it this
way

Sub Kevin2()
Dim oActiveSheet As Object



Sheets("Duplicate").Copy Befo=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet

With Workbooks("2006.05.30Presentation.xls")
oActiveSheet.Move after:=.Sheets(.Sheets.Count)
End With
End Sub


--
Regards,
Tom Ogilvy



Hi Tom,

Thank you. I obviously hadn't realized that my count was counting the wrong
sheets in the wrong book.

Thank you!

Best regards,
Kevin

Moving Sheets To A Different Workbook XL2003
 

Tom Ogilvy wrote...
the unqualified Sheets.count refers to the active workbook try it this
way

Sub Kevin2()
Dim oActiveSheet As Object



Sheets("Duplicate").Copy Befo=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet

With Workbooks("2006.05.30Presentation.xls")
oActiveSheet.Move after:=.Sheets(.Sheets.Count)
End With
End Sub


--
Regards,
Tom Ogilvy



Tom,

A follow up question...

Sub Kevin2()
Dim oActiveSheet As Object

For loop
Do a a bunch of stuff on Book1.xls using Book1's sheets.


Sheets("Duplicate").Copy Befo=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet

With Workbooks("2006.05.30Presentation.xls")
oActiveSheet.Move after:=.Sheets(.Sheets.Count)
End With

Next Statement

End Sub

With the "With Workbooks" statement, I suspect "2006.05.30Presentation.xls"
is now my active workbook. So now my For Loop breaks because the code is
expecting that I am continuing to work with Book1.xls. How do I make
Book1.xls my active workbook again so that my for loop works?

Thank you.

Best regards,
Kevin

Moving Sheets To A Different Workbook XL2003
 

Hi Kevin,

There seems to be too much confusion about sheet and workbook references. I
don't understand why you need to copy the sheet into the active wbk to just
then move it to the other wbk when you could copy it directly there quite
easily.

This might help clear things up:

Sub SheetToOtherWbk()
Dim wbkTarget As Workbook
Dim wksSource As Worksheet

Set wksSource = ActiveSheet
Set wbkTarget = Workbooks("2006.05.30Presentation.xls")

wksSource.Copy after:=wbkTarget.Sheets(wbkTarget.Sheets.Count)

End Sub

Regards,
Garry

Moving Sheets To A Different Workbook XL2003
 

Hi Garry,

Here's my difficulty. Further below is my complete routine (it's pretty
simple) with "blahs" inserted for range names.

If I leave it as is, it hangs up on the start of second loop with the line:
Sheets("Input").Range("blah1").Value = iCounter1

If I comment out the following lines:

With Workbooks("2006.05.30Presentation.xls")
wkshtActiveSheet.Move after:=.Sheets(.Sheets.Count)
End With

Then it doesn't hang. So something is happening with the "With Workbooks"
statements that causes the error 9. My thoughts are that there is no
"Input" sheet in "2006.05.30Presentation.xls". Somehow, I need to make the
original workbook active again.

To summarize my difficulty, the routine gets hung up with the "With
Statement" included. I believe that I need to reactivate the original
workbook in order not to get hung up on the do loop.

I hope that helps to clarify.

Sub GenCases1()
Dim iCounter1 As Integer
Dim wkshtActiveSheet As Worksheet

For iCounter1 = 1 To 3 Step 1


Sheets("Input").Range("blah1").Value = iCounter1

Sheets("Input").Range("blah2").Value = 2
Sheets("Hidden").Range("blah3").Value = 3
Sheets("Hidden").Range("blah4").Value = 3
Sheets("Hidden").Range("blah5").Value = 0


Sheets("Duplicate").Copy Befo=Sheets("Duplicate")
Set wkshtActiveSheet = Sheets("Duplicate (2)")

wkshtActiveSheet.Name = "blah6" & wkshtActiveSheet.Range("blah7").Value

wkshtActiveSheet.Range("blah8").Copy
wkshtActiveSheet.Range("blah8").PasteSpecial Paste:=xlPasteValues

wkshtActiveSheet.Range("blah9").Copy
wkshtActiveSheet.Range("blah9").PasteSpecial Paste:=xlPasteValues

wkshtActiveSheet.Range("blah10").Copy
wkshtActiveSheet.Range("blah10").PasteSpecial Paste:=xlPasteValues

Sheets("Hidden").Range("blah3").Value = 4

Sheets("Hidden").Range("blah5").Value = 3

wkshtActiveSheet.Range("blah11").Copy
wkshtActiveSheet.Range("blah11").PasteSpecial Paste:=xlPasteValues

With Workbooks("2006.05.30Presentation.xls")
wkshtActiveSheet.Move after:=.Sheets(.Sheets.Count)
End With

Next iCounter1

End Sub



Best regards,
Kevin

Moving Sheets To A Different Workbook XL2003
 

Hi Kevin,

Try this:

Sub GenCases1Revised()
Dim i As Integer
Dim wksTarget As Worksheet
Dim wbkSource As Workbook, wbkTarget As Workbook

'Get qualified references
Set wbkSource = ActiveWorkbook
'If this is the same workbook that has this code,
'you could use ThisWorkbook instead of using a variable,

Set wbkTarget = Workbooks("2006.05.30Presentation.xls")

For i = 1 To 3

'Set the qualified reference here
With wbkSource
'With ThisWorkbook
.Sheets("Input").Range("blah1").Value = i
.Sheets("Input").Range("blah2").Value = 2

.Sheets("Hidden").Range("blah3").Value = 3
.Sheets("Hidden").Range("blah4").Value = 3
.Sheets("Hidden").Range("blah5").Value = 0

.Sheets("Duplicate").Copy befo=.Sheets("Duplicate")
'It's now the active sheet so assign it to the variable
Set wksTarget = ActiveSheet
'OR you could refer to it directly as ActiveSheet
'without using a variable

With wksTarget
'With ActiveSheet
.Name = "blah6" & .Range("blah7").Value
.Range("blah8").Value = .Range("blah8").Value
.Range("blah9").Value = .Range("blah9").Value
.Range("blah10").Value = .Range("blah10").Value
.Range("blah11").Value = .Range("blah11").Value
.Move after:=wbkTarget.Sheets(wbkTarget.Sheets.Count)
End With

.Sheets("Hidden").Range("blah3").Value = 4
.Sheets("Hidden").Range("blah5").Value = 3
End With
Next

End Sub

HTH
Regards,
Garry

Moving Sheets To A Different Workbook XL2003
 

Sub Kevin2()
Dim oActiveSheet As Object
Dim oBk as Workbook
set oBk = ActiveWorkbook
For loop
Do a a bunch of stuff on Book1.xls using Book1's sheets.


Sheets("Duplicate").Copy Befo=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet

With Workbooks("2006.05.30Presentation.xls")
oActiveSheet.Move after:=.Sheets(.Sheets.Count)
End With
bk.Activate

Next Statement

End Sub

--
Regards,
Tom Ogilvy


"Harry Sampson" wrote in message
...
Tom Ogilvy wrote...
the unqualified Sheets.count refers to the active workbook try it this
way

Sub Kevin2()
Dim oActiveSheet As Object



Sheets("Duplicate").Copy Befo=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet

With Workbooks("2006.05.30Presentation.xls")
oActiveSheet.Move after:=.Sheets(.Sheets.Count)
End With
End Sub


--
Regards,
Tom Ogilvy



Tom,

A follow up question...

Sub Kevin2()
Dim oActiveSheet As Object

For loop
Do a a bunch of stuff on Book1.xls using Book1's sheets.


Sheets("Duplicate").Copy Befo=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet

With Workbooks("2006.05.30Presentation.xls")
oActiveSheet.Move after:=.Sheets(.Sheets.Count)
End With

Next Statement

End Sub

With the "With Workbooks" statement, I suspect
"2006.05.30Presentation.xls"
is now my active workbook. So now my For Loop breaks because the code is
expecting that I am continuing to work with Book1.xls. How do I make
Book1.xls my active workbook again so that my for loop works?

Thank you.

Best regards,
Kevin

Moving Sheets To A Different Workbook XL2003
 

Tom Ogilvy wrote...
Sub Kevin2()
Dim oActiveSheet As Object
Dim oBk as Workbook
set oBk = ActiveWorkbook
For loop
Do a a bunch of stuff on Book1.xls using Book1's sheets.


Sheets("Duplicate").Copy Befo=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet

With Workbooks("2006.05.30Presentation.xls")
oActiveSheet.Move after:=.Sheets(.Sheets.Count)
End With
bk.Activate

Next Statement

End Sub


Hi Tom,

Thank you very much!!

Best regards,
Kevin