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