none
Excel 2016 VBA activeworkbook actions are not done on activeworkbook once we change to thisworkbook RRS feed

  • Question

  • 
    I'm trying to copy data from workbook where macro code is written to a new workbook and renaming the sheet. Code works fine in 2013 but same code throws error when I try to change sheet name that Name is already used ( windows 10 office 2016)
    
    Same code works fine in 2013 and in debug mode in 2016.
    
    Below is the sample code which is similar to my requirement.
    
    
    Sub test()
    Range("a" & Rows.Count).End(xlUp).Offset(1, 0) = "This workbook"
    Application.ScreenUpdating = False
    Set newwb = Workbooks.Add
    Worksheets.Add
    Worksheets.Add
    For i = 0 To 50
    
    newwb.Activate
    Range("a" & Rows.Count).End(xlUp).Offset(1, 0) = "activeworkbook workbook"
    ActiveWorkbook.Sheets(1).Name = "test"
    newwb.Activate   '--> I don't need to add this code but just to check if it works and still the same issue. 
    ActiveWorkbook.Sheets(2).Name = "test2"
    ActiveWorkbook.Sheets(3).Name = "test3"
    ThisWorkbook.Activate
    Range("a" & Rows.Count).End(xlUp).Offset(1, 0) = "This workbook"
    Next
    
    End Sub
    Can you say why it is throwing the error?
    Code works perfectly fine in windows 7 excel 2013 but same code throws error in 2016.
    
    It would be great if anyone can help me with this issue.

    Sunday, August 20, 2017 4:05 PM

All replies

  • I can run your code in Excel 2016 (on Windows 10, but that shouldn't make a difference) without error.

    But here is a safer version of the code that doesn't rely on activating workbooks:

    Sub test()
        Dim newwb As Workbook
        Dim newws As Worksheet
        Dim thisws As Worksheet
        Dim i As Long
        Application.ScreenUpdating = False
        Set thisws = ThisWorkbook.Worksheets(1)
        thisws.Range("A" & thisws.Rows.Count).End(xlUp).Offset(1, 0) = "This workbook"
        Set newwb = Workbooks.Add(xlWBATWorksheet)
        newwb.Worksheets.Add
        newwb.Worksheets.Add
        Set newws = newwb.Worksheets(1)
        For i = 0 To 50
            newws.Range("A" & newws.Rows.Count).End(xlUp).Offset(1, 0) = "activeworkbook workbook"
            newws.Name = "test"
            newwb.Sheets(2).Name = "test2"
            newwb.Sheets(3).Name = "test3"
            thisws.Range("A" & thisws.Rows.Count).End(xlUp).Offset(1, 0) = "This workbook"
        Next i
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, August 20, 2017 5:14 PM
  • Hi,

    I notice that the issue is related to the Macro codes in Excel. For further troubleshooting, I would move the thread to Excel for Developers forum for more help.

    Thanks for your understanding.

    Regards,

    Winnie Liang


    Please remember to mark the replies as answers if they help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, August 21, 2017 2:18 AM
  • thank for reply. This way it would work ,but I want to know why it is causing the issue when it works fine in 2013? its simple activeworkbook which works fine in old version.

    Tuesday, August 22, 2017 12:32 PM
  • I'm sorry, I don't know. There's not much difference between Excel 2013 and Excel 2016...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 22, 2017 3:11 PM