Moving Sheets To A Different Workbook XL2003

[Kevin H. Stecyk]

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 Before:=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet

oActiveSheet.Move
after:=Workbooks("2006.05.30Presentation.xls").Sheets(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

 

[Guest]

I did the below mopd and your code worked

Sub Blah()

Dim oActiveSheet As Object
Dim x As Long

Sheets("Duplicate").Copy Before:=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet
x = Workbooks("2006.05.30Presentation.xls").Sheets.Count
oActiveSheet.Move after:=Workbooks("2006.05.30Presentation.xls").Sheets(x)

End Sub

 

[Guest]

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

Sub Kevin2()
Dim oActiveSheet As Object



Sheets("Duplicate").Copy Before:=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet

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

 

[Kevin H. Stecyk]

ADG wrote...

I did the below mopd and your code worked

Sub Blah()

Dim oActiveSheet As Object
Dim x As Long

Sheets("Duplicate").Copy Before:=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet
x = Workbooks("2006.05.30Presentation.xls").Sheets.Count
oActiveSheet.Move
after:=Workbooks("2006.05.30Presentation.xls").Sheets(x)

End Sub

Thank you Tony. I see my error.

Best regards,
Kevin

 

[Kevin H. Stecyk]

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 Before:=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet

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

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

 

[Harry Sampson]

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 Before:=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet

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

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 Before:=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

 

[Guest]

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

 

[Kevin H. Stecyk]

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 Before:=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

 

[Guest]

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 before:=.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

 

[Tom Ogilvy]

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 Before:=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

 

[Kevin H. Stecyk]

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 Before:=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