Which workbook is created with Sheets(XX).Copy?

    Does anyone know how to reference the new workbook that is created when you use the sheets(sheet1).copy without a before or after qualifyer?


    It looks like it is just Book(n).xls where n is an incriment on the last new workbook created. I know I could compare the list of open workbooks before and after this event but that seems a bit messy. Is there a way I can assign the new book to an object variable? (have tried Set ObjectVar = sheets(sheet1).copy - no luck :( )

    It will be the Active workbook by default so you could reference it as follows


    Code
    Sub copyeg()
Dim newwb As Workbook
Sheets(1).Copy
Set newwb = ActiveWorkbook
MsgBox "" & newwb.Name
End Sub


    Hope this helps

    Thanks! That was roughly the direction I was going, I was just nervous that something could 'pinch' the ActiveWorkbook before it was assigned to the ObjectVar.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login