Hi,
I have started a new thread on this problem, my other thread got
a little lost and I was not getting the right answers. Basically I
need to copy sheets from 'n' different closed workbooks into my open
workbook from where the macro is being executed, this new sheet needs
to be the last sheet in my workbook, here is the copying code I am
using:

sourceBk.Worksheets(y).Copy _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksh eets.Count)

The source file (closed workbooks) is ok, it reads this fine, what I
cannot work out is how to reference my open workbook, the code above
does not work, I have also tried using 'ActiveWorkbook' but it does
not like this either. the full code I am using is shown below.

Sub import_xls()
Dim y As Integer
Dim d As Integer
Dim p As Integer


Folder = "F:\My Documents\Fantasy Football\XLS_Emails\"
FName = Dir(Folder & "*.xls")
Application.ScreenUpdating = False
Do While FName < ""
d = 0
With ThisWorkbook
Set sourceBk = Workbooks.Open(Filename:=Folder & FName)
For y = 1 To sourceBk.Worksheets.Count
If Left(sourceBk.Worksheets(y).Cells(1, 1), 4) = "Name" Then
d = d + 1
MsgBox "FOUND A VALID TEAMSHEET " &
sourceBk.Worksheets(y).Cells(1, 2) & " IN:" & FName
For p = 8 To 18
If InStr(1, sourceBk.Worksheets(y).Cells(p, 2), 1) < "" Then
'MsgBox "PLAYER CELL POPULATED OK: " & p
Else
MsgBox "ERROR: EMPTY PLAYER CELL IN: " &
sourceBk.Workheets(y).Cells(p, 2)
Exit Sub
End If
Next p

Else
'MsgBox "UN-MATCHED TEAMSHEET:" & FName
End If

If d = 1 Then
MsgBox "CREATING NEW WORKSHEET FOR: " &
sourceBk.Worksheets(y).Cells(1, 2)

sourceBk.Worksheets(y).Copy _

After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksh eets.Count)
sourceBk.Close savechanges:=False

End If
Next y
End With
Application.ScreenUpdating = True

FName = Dir()
Loop
End Sub

Set a reference to your workbook before you start and use that....

Dim wbMaster as Workbook
Set wbMaster = ActiveWorkbook

Use wbMaster as your reference

--

Regards,
Nigel




"tommo_blade" wrote in message
...
Hi,
I have started a new thread on this problem, my other thread got
a little lost and I was not getting the right answers. Basically I
need to copy sheets from 'n' different closed workbooks into my open
workbook from where the macro is being executed, this new sheet needs
to be the last sheet in my workbook, here is the copying code I am
using:

sourceBk.Worksheets(y).Copy _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksh eets.Count)

The source file (closed workbooks) is ok, it reads this fine, what I
cannot work out is how to reference my open workbook, the code above
does not work, I have also tried using 'ActiveWorkbook' but it does
not like this either. the full code I am using is shown below.

Sub import_xls()
Dim y As Integer
Dim d As Integer
Dim p As Integer


Folder = "F:\My Documents\Fantasy Football\XLS_Emails\"
FName = Dir(Folder & "*.xls")
Application.ScreenUpdating = False
Do While FName < ""
d = 0
With ThisWorkbook
Set sourceBk = Workbooks.Open(Filename:=Folder & FName)
For y = 1 To sourceBk.Worksheets.Count
If Left(sourceBk.Worksheets(y).Cells(1, 1), 4) = "Name" Then
d = d + 1
MsgBox "FOUND A VALID TEAMSHEET " &
sourceBk.Worksheets(y).Cells(1, 2) & " IN:" & FName
For p = 8 To 18
If InStr(1, sourceBk.Worksheets(y).Cells(p, 2), 1) < "" Then
'MsgBox "PLAYER CELL POPULATED OK: " & p
Else
MsgBox "ERROR: EMPTY PLAYER CELL IN: " &
sourceBk.Workheets(y).Cells(p, 2)
Exit Sub
End If
Next p

Else
'MsgBox "UN-MATCHED TEAMSHEET:" & FName
End If

If d = 1 Then
MsgBox "CREATING NEW WORKSHEET FOR: " &
sourceBk.Worksheets(y).Cells(1, 2)

sourceBk.Worksheets(y).Copy _

After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksh eets.Count)
sourceBk.Close savechanges:=False

End If
Next y
End With
Application.ScreenUpdating = True

FName = Dir()
Loop
End Sub

I am still getting the same problem, i.e. an error with the following
lines:-

sourceBk.Worksheets(y).Copy _
After:=wbMaster.Worksheets(wbMaster.Worksheets.Cou nt)

I have added the code at the very top to set 'wbMaster' as you stated,
so:-

Sub import_xls()
Dim y As Integer
Dim d As Integer
Dim p As Integer
Dim c As Integer
Dim wbMaster As Workbook

Set wbMaster = ActiveWorkbook
Folder = "F:\My Documents\Fantasy Football\XLS_Emails\"
FName = Dir(Folder & "*.xls")
Application.ScreenUpdating = False

<< MORE CODE HERE



thanks in advance for any assistance, Mark.

Your code should work, and works for me. But only if var Y has a valid index
number for a sheet in the sourceBk.

Have you checked this value?

--

Regards,
Nigel




"tommo_blade" wrote in message
...
I am still getting the same problem, i.e. an error with the following
lines:-

sourceBk.Worksheets(y).Copy _
After:=wbMaster.Worksheets(wbMaster.Worksheets.Cou nt)

I have added the code at the very top to set 'wbMaster' as you stated,
so:-

Sub import_xls()
Dim y As Integer
Dim d As Integer
Dim p As Integer
Dim c As Integer
Dim wbMaster As Workbook

Set wbMaster = ActiveWorkbook
Folder = "F:\My Documents\Fantasy Football\XLS_Emails\"
FName = Dir(Folder & "*.xls")
Application.ScreenUpdating = False

<< MORE CODE HERE



thanks in advance for any assistance, Mark.

It does, I printed the value of 'y' just prior to the copy statemet
and this was '6' which is exactly the sheet I need in the source
workbook, you can also see in my code another print statement just
prior to the 'Copy' function - this prints the value of a cell (1,2)
in that sheet and also returns the correct data:-

MsgBox "CREATING NEW WORKSHEET FOR: " &
sourceBk.Worksheets(y).Cells(1, 2)
MsgBox "Y: " & y
sourceBk.Worksheets(y).Copy _
After:=wbMaster.Worksheets(wbMaster.Worksheets.Cou nt)

puzzling..

Your original code using thisworkbook was correct. There is no need to create
an object varaible. Thisworkbook is always the workbook where the code is
running from.
There is nothing specificly wrong with the line of code that you have. What
error are you getting. If it is subscript out of range then I would suggest
that you are trying to reference a worksheet that does not exist.

I am curious why you are using y for a variable instead of using a worksheet
object?
Change
For y = 1 To sourceBk.Worksheets.Count
using index numbers is very difficult to debug...
to

dim wksSource as worksheet
for each wksSourse in sourceBk.Worksheets
'your code directly referenceing the worksheet
wksSource.copy After:=ThisWorkbook.worksheet(thisworkbook.workshe et.count)
next wksSource
--
HTH...

Jim Thomlinson


"tommo_blade" wrote:

It does, I printed the value of 'y' just prior to the copy statemet
and this was '6' which is exactly the sheet I need in the source
workbook, you can also see in my code another print statement just
prior to the 'Copy' function - this prints the value of a cell (1,2)
in that sheet and also returns the correct data:-

MsgBox "CREATING NEW WORKSHEET FOR: " &
sourceBk.Worksheets(y).Cells(1, 2)
MsgBox "Y: " & y
sourceBk.Worksheets(y).Copy _
After:=wbMaster.Worksheets(wbMaster.Worksheets.Cou nt)

puzzling..