Sheets.copy problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jln - 07 Dec 2007 13:32 GMT

I need to copy all sheets from one work book to a main wookbook. The main
workbook is already open and it will open the 2nd work book with the tabs i
need to copy from.

Reply to this Message

Joel - 07 Dec 2007 13:55 GMT

This code also perform a check to make surre the sheets don't existt in the
main book

Sub copysheets()

Workbooks.Open Filename:="second.xls"
For Each second_ws In Workbooks("second.xls").Sheets
Found = False
For Each main_ws In ThisWorkbook.Sheets
If second_ws.Name = main_ws.Name Then
Found = True
Exit For
End If
Next main_ws
If Found = False Then
With ThisWorkbook
second_ws.Copy after:=.Sheets(.Sheets.Count)
End With
End If

Next second_ws
End Sub

> I need to copy all sheets from one work book to a main wookbook. The main
> workbook is already open and it will open the 2nd work book with the tabs i
> need to copy from.

Reply to this Message

jln - 07 Dec 2007 14:08 GMT

Joel

Im trying your code but i had to chage the file to this and now im getting a
sub script out of range

Sub othertabs(Inv As Integer)
Dim second_ws As Workbook
Dim Found As Integer
Dim main_ws As Workbook

Set main_ws = ActiveWorkbook

FileName = Dir("S:\Iashare\0Subprime\Tracking\AA\" & Inv & ".xls")

Workbooks.Open FileName:="S:\Iashare\0Subprime\Tracking\AA\" & Inv & ".xls"

Set second_ws = ActiveWorkbook

For Each second_ws In Workbooks(" & FileName & ").Sheets
Found = False
For Each main_ws In ThisWorkbook.Sheets
If second_ws.Name = main_ws.Name Then
Found = True
Exit For
End If
Next main_ws
If Found = False Then
With ThisWorkbook
second_ws.Copy after:=.Sheets(.Sheets.Count)
End With
End If

Next second_ws

>This code also perform a check to make surre the sheets don't existt in the
>main book
[quoted text clipped - 22 lines]
>> workbook is already open and it will open the 2nd work book with the tabs i
>> need to copy from.

Reply to this Message

Joel - 07 Dec 2007 14:34 GMT

Try these changes

Sub othertabs(Inv As Integer)
Dim second_ws As Workbook
Dim Found As Integer
Dim main_ws As Workbook

Set main_ws = ActiveWorkbook
folder = "S:\Iashare\0Subprime\Tracking\AA\"
Filename = Dir(folder & Inv & ".xls")

Workbooks.Open Filename:=folder & Filename

Set second_ws = ActiveWorkbook

For Each second_ws In second_ws.Sheets
Found = False
For Each main_ws In ThisWorkbook.Sheets
If second_ws.Name = main_ws.Name Then
Found = True
Exit For
End If
Next main_ws
If Found = False Then
With ThisWorkbook
second_ws.Copy after:=.Sheets(.Sheets.Count)
End With
End If

Next second_ws
End Sub

> Joel
>
[quoted text clipped - 56 lines]
> >> workbook is already open and it will open the 2nd work book with the tabs i
> >> need to copy from.

Reply to this Message

jln - 07 Dec 2007 14:51 GMT

Joel

Still not having any luck Im not sure if this will make things easier but the
2 work book will never have the same sheet names.

>Try these changes
>
[quoted text clipped - 33 lines]
>> >> workbook is already open and it will open the 2nd work book with the tabs i
>> >> need to copy from.

Reply to this Message

Joel - 07 Dec 2007 15:00 GMT

Are you calling this subroutine from another subroutine? Other tabs need the
parameter Inv. Make sure you are caling the sub like main below

sub main
Inv = "abc"
call other(Inv)
end sub

> Joel
>
[quoted text clipped - 38 lines]
> >> >> workbook is already open and it will open the 2nd work book with the tabs i
> >> >> need to copy from.

Reply to this Message

jln - 07 Dec 2007 15:17 GMT

It is being called from another subroutine. All of the other Subroutines that
i have are being passed the Inv number

Call othertabs(Inv)

>Are you calling this subroutine from another subroutine? Other tabs need the
>parameter Inv. Make sure you are caling the sub like main below
[quoted text clipped - 9 lines]
>> >> >> workbook is already open and it will open the 2nd work book with the tabs i
>> >> >> need to copy from.

Reply to this Message

Joel - 07 Dec 2007 15:42 GMT

These changes should work

Sub othertabs(Inv As Integer)
Dim second_ws As Workbook
Dim Found As Integer
Dim main_ws As Workbook

Set main_ws = ActiveWorkbook
folder = "S:\Iashare\0Subprime\Tracking\AA\"
Filename = Dir(folder & Inv & ".xls")

Workbooks.Open Filename:=folder & Filename

Set second_ws = ActiveWorkbook

For Each ws In second_ws.Sheets
Found = False
For Each mn_ws In ThisWorkbook.Sheets
If second_ws.Name = mn_ws.Name Then
Found = True
Exit For
End If
Next mn_ws
If Found = False Then
With ThisWorkbook
ws.Copy after:=.Sheets(.Sheets.Count)
End With
End If

Next ws
End Sub

> It is being called from another subroutine. All of the other Subroutines that
> i have are being passed the Inv number
[quoted text clipped - 14 lines]
> >> >> >> workbook is already open and it will open the 2nd work book with the tabs i
> >> >> >> need to copy from.

Reply to this Message