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

    Sub copyeg()
    Dim newwb As Workbook
    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!