一旦我们更改为本工作簿，Excel 2016 VBA activeworkbook操作不会在activeworkbook上完成 [英] Excel 2016 VBA activeworkbook actions are not done on activeworkbook once we change to thisworkbook

问题描述

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.

推荐答案

我可以在Excel 2016中运行您的代码（在Windows 10上，但这不应该有所作为）错误。

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:

子测试（）

    Dim newwb As Workbook

    Dim newws As Workheet

    Dim thisws As Worksheet

    Dim i As Long

    Application.ScreenUpdating = False

   设置thisws = ThisWorkbook.Worksheets（1）

    thisws.Range（" A"& thisws.Rows.Count）.End（xlUp）.Offset（1,0）=" This workbook" b $ b   设置newwb = Workbooks.Add（xlWBATWorksheet）

    newwb.Worksheets.Add

    newwb.Worksheets.Add

   设置newws = newwb.Worksheets（1）

   对于i = 0至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" b $ b   接下来我是
    Application.ScreenUpdating = True

End Sub

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

这篇关于一旦我们更改为本工作簿，Excel 2016 VBA activeworkbook操作不会在activeworkbook上完成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！