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.

--
Message posted via http://www.officekb.com

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


"jln via OfficeKB.com" wrote:

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.

--
Message posted via http://www.officekb.com

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


Joel wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200712/1

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


"jln via OfficeKB.com" wrote:

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


Joel wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200712/1

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.

Joel wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200712/1

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

"jln via OfficeKB.com" wrote:

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.

Joel wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200712/1