Sheets.copy problem

  • Thread starter Thread starter jln via OfficeKB.com
  • Start date Start date
J

jln via OfficeKB.com

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.
 
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
 
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
 
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

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

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
Click to expand...
[quoted text clipped - 56 lines]
Click to expand...
 
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 said:
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

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
Click to expand...
[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.
Click to expand...
Click to expand...
Click to expand...
 
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

sub main
Inv = "abc"
call other(Inv)
end sub
Joel
Click to expand...
[quoted text clipped - 38 lines]
Click to expand...
 
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



jln via OfficeKB.com said:
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

sub main
Inv = "abc"
call other(Inv)
end sub
Joel
Click to expand...
[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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top